Look for the first space from the right side of a cell

**whatsmyname** · 02-19-2013, 06:55 PM

Hello all,

I am trying to remove the text from a string from the right side of a cell. Basically it will start from the right and look for the first space (" ") and then it will copy those characters into the next coulum and then remove them from the original string in the original cell. I have attached an example of what I am trying to accomplish. With the original text in coloum A.

Your help would be greatly appreciated.

Thank you.

**dredwolf** · 02-19-2013, 07:09 PM

I think you need something like this :
=RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Edit-
see attachment

**benishiryo** · 02-19-2013, 09:49 PM

hi there. if all you wanted to do was to achieve column D, then:
=SUBSTITUTE(A1," "," #",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))

if you need column B, then maybe:
=LEFT(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)

to find the characters after the last space, you can use dredwolf's. if your last space of your data is consistently after " B ", then the formula could be slightly shorter.

**dredwolf** · 02-20-2013, 11:34 AM

Correction to my original File :New Microsoft Excel Worksheetsol2.xlsx

**Kevin UK** · 02-20-2013, 02:27 PM

Hi whatsmyname

Another option:

Formula:

Please Login or Register  to view this content.

If you need the text in columns B & C, You can do Text to Columns > Fixed Width > Position the break lines.

**whatsmyname** · 02-22-2013, 11:21 AM

Wow,

Thank you all! dredwolf, can you explain your formula in detail for me? All of these options work great. I just would like to understand the formulas so I can learn myself.

Also, dredwolf, how did you get it to automatically fill in columns B,C, & D, after the string is entered into column A?

**dredwolf** · 02-22-2013, 02:48 PM

Okay, Using the revised Formulas posted in Second workbook

Formula:

Please Login or Register  to view this content.

B1 formula
LEN(A1)-LEN(SUBSTITUTE(A1," ","")); this calculates how many spaces are in the string in A1 by taking the length of a1 with spaces and subtracting the lenght of the string with no spaces (the substitute replaced all the spaces with nothing("")), this gives us the number of the space to Substitute with the '~' in this :SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))) ( the blue part is the optional paramater for Substitute telling it which occurrence of the 'old text'(" ") to replace with 'new text'("~"))
The LEFT(A1,FIND("~",.. part tells Excel to take the Left part af A1, with the find("~",....)-1) telling it how many characters to use
SO.. "Find the position of the tilde in the modified(through the substitutes) A1 String,subtract 1, and return that many of the characters from the Left of A1"

C1 formula
is pretty much the same, but for the right side of A1
we want all the characters Beyond the last space, so return Lenght of A1 minus the position of the "~"(last space) # of characters from the end of A1

D1 formula
This simply Creates the new string from the results : "take B1 add " #" to the end of that, then add C1 to the end of that" (the '&' is a shortcut for concatenate)

The way the auto fill works is because of the Relative Addressing (with $A$1(Absolute Addressing), the formula would always look at cell A1),by not using the '$' sign, the A1 changes to A2,A3,A4... as you drag the formulas down the columns

Hope I didn't confuse you TOO much there