Question using LEFT, MID, or RIGHT function

**Ziggy4e** · 05-26-2013, 02:20 PM

The following are all in column A1-A3

Alabama 150,000
Alaska 28,909
Arizona 211,904

I have a really lost list. I am just trying to get it so the state will end up in 1 column and the number in the other column.

I used =LEFT(A1,FIND(",",A1,1)-4) to find the state but I cant figure out how to extract the number into a different column.

Thanks for the help

**aydeegee** · 05-26-2013, 02:44 PM

Hi Ziggy4e

try

Please Login or Register  to view this content.

Regards
Alastair

**Pepe Le Mokko** · 05-26-2013, 02:52 PM

A non formula alternative : Data -text to columns - Delimited - Next - Select space as delimiter - Finish

**Ziggy4e** · 05-26-2013, 03:05 PM

Originally Posted by aydeegee

Hi Ziggy4e

try

Please Login or Register  to view this content.

Regards
Alastair

Thanks for the formula. It worked except for when I ran into States with spaces in them like New Hampshire and North Dakota...

**Pepe Le Mokko** · 05-26-2013, 03:18 PM

This will extract the last part of any string, delimiter being a space

Please Login or Register  to view this content.

**Tony Valko** · 05-26-2013, 04:31 PM

Try this...

Enter this formula in C1:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

Enter this formula in B1:

=TRIM(SUBSTITUTE(A1,C1,""))

Select B1:C1 and copy down as needed.

With the range of formulas still selected do:

Right click>Copy
Right click>Paste Special>Values>OK

Inspect the results to make sure you got what you wanted.

Delete column A

Select the range of values in column B.

Goto Data>Text to Columns
Click Finish