# Question using LEFT, MID, or RIGHT function

1. ## Question using LEFT, MID, or RIGHT function

The following are all in column A1-A3

Alabama 150,000
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

2. ## Re: Question using LEFT, MID, or RIGHT function

Hi Ziggy4e

try
``Please Login or Register  to view this content.``
Regards
Alastair

3. ## Re: Question using LEFT, MID, or RIGHT function

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

4. ## Re: Question using LEFT, MID, or RIGHT function

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...

5. ## Re: Question using LEFT, MID, or RIGHT function

This will extract the last part of any string, delimiter being a space
``Please Login or Register  to view this content.``

6. ## Re: Question using LEFT, MID, or RIGHT function

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

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1