Extract Text From Nth Occurrence of a Space in a Cell

Показать описание

Extract text from the nth occurrence of a space in a cell.

This video looks at how to extract some text from another cell by looking for the 3rd occurrence of a space in the cell. The characters we want to extract occur after the 3rd space within the cell.

By using the MID function along with FIND and SUBSTITUTE we can achieve this complex operation. Once written this will work for all the records in a list.

Find more great free tutorials at;

*** Online Excel Courses ***

Connect with us!

Рекомендации по теме

Комментарии

Excellent tutorial, Computergaga, the explanation can't be clearer. Thanks for sharing your knowledge!!

aguerojg

Super helpful! Very easy to follow and understand - thank you

veritymorris

THANK YOU! This is the ONLY video that has helped!

sandivant

a 7-year-old video I stumbled upon which helped me immensely! thank you!

babiechad

This video was awesome. Normally these tutorials bore me a bit... This one was so entertaining, haha. Love your energy!

arq.gabrielamartinez

What a great solution suggested. Thanks a lot. I had been working hard to solve such issues. In spite of my many years of experience, mathematical skills I could never find a right solution. I have developed formulas but they are very clumsy and involving so many columns. Sir, Thanks a lot as it is solved in just one statement. Excellent Tutorial

harshadvegda

Nice approach Computergaga.
Before assist your video until the end, I tried my own approach where I should extract all the text before the numbers. So this was the formula I came up.
{=left(A1, small(if(--(mid(A1, row(indirect("1:"&len(A1))), 1)=" ")*row(indirect("1:"&len(A1)))=0, " ", row(indirect("1:"&len(A1)))), 3)-1)}

BTW nice accent. I had no problem to understand you.

vandalo

Oh, thanks Computergaga. Thank God, I found this video. I was looking for this for several days. The trick you shared with us is really helpful. Keep up the sharing!

jtr

What about if the "3" isn't fixed.... but it could be; 3, 5, 12, 1, 4 spaces from the left?

alexstrassel

came for the lesson stayed for the accent. this is brilliant and helpful; you approached it with the right attitude.

bayareanonsense

This is great thanks for replying so quickly. Do you know how to do exactly this but with a Regextract formula? This formula doesn't translate well to Google data studio unfortunately

kinkineki

One question here we are hard coding the number of characters to be extracted to 2. If my length is varying, how to make it dynamic. Like i have numbers like 38, 532, 6554. I want the code to extract these.

soumyamohapatra

Thanks.. Your video really helped me a lot. But what if the length of the extracted data is variable. We cannot put 2 in that case. Please help!!

ankanjyoti

Thanks sir, very helpful video. But I want to know whether we can extract more than one word. You have taught to extract one word from the middle of a line, in this video, but I want to get more than one word from a line.

kameshsharma

Hi Computergaga, i am in need of some help, on this topic. the video gives a perfect explanation of how to get the information that i need from the string of text. but my string of text gets generated online, and will be refreshed daily where the numbers that i need extracted from the text will also change, in the way that the numbers get larger daily. is there a way to setup the formula so that it will automatically extract the number no matter how many characters it is?

janlaubscher

Howdo you extract first, middle, last names and suffix such as Jr. into separate columns. Not everyone has a suffix nor does everyone have a middle name?

robertbarker

Good job, but maybe you need a certain text like 3542 or 5 .... there you go as an assist to this problem :
=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) *

jumbyto

Hello. Instead of extracting, what function would you use to delete those two characters?

GilCuriously

sir I have some problem in excel I have a hudge data of names that is one lakh plus I want to found those names which have no space

imranali-iywk

If number start with on letter also please

majeedkhan

Extract Text From Nth Occurrence of a Space in a Cell

Extract Text From Nth Occurrence of a Space in a Cell

How to find the nth occurrence of a text in Excel - Office 365

Finding nth Occurrence in Excel

Excel Lookup and Find the 2nd, 3rd, 4th or Nth Occurrence / Match (Without Using an Array Formula)

How to Get the Position of the nth Occurrence of a Character in a Cell

How To Find Nth Occurrence (Position) Of A Character In Text String In Excel?

Extract Text from Cell: How to extract substring after specific instance of a specific character

How to get the position of nth occurrence of a value in column

How to Remove Text Before the N-th Occurrence of a Character in Excel | Excelgradaute

How to Remove Text After the N-th Occurrence of a Specific Character in Excel | Excelgraduate

Counting the Nth Occurrence of a Value in Excel

Find position of Nth occurrence of search character in source string using SQL in IBM i DB2

Count the Number of Occurrence of a Text or Number in Excel

#Shorts | VLOOKUP to find the nth Order Occurrence

Find the Nth instance of a text string within a range?

Text file to fields (columns) every nth occurrence of a string (3 Solutions!!)

JavaScript : How to get the nth occurrence in a string?

SQL Tricks | How to Extract String After the Last Occurrence of Delimiter ? REVERSE

SQL to Count Occurrence of a Character/Word in a String

Replace on Nth Occurrence (2 Solutions!!)

PYTHON : Find the nth occurrence of substring in a string

Extract MID between characters, but from second instance in Excel

Python Interview Question | Program to Remove Nth Occurrence of the Given Word | Perfect eLearning

Find the position of last occurrence of a specific character from a text string in Excel