Text to Columns

[chowdown08]

Hi All. I've been trying to automate a process that I have to do every day at work without having to use VBA. Hoping someone can help.

I import data into a column that has a string of text followed by a hyphen and 8 numbers. An example would be "Sonoma Country - 13456-094". My goal is to take the 8 numbers "*****-***" and put it in the column over. A1 would then be "Sonoma Country" & B1 would be 13456-094.

I usually find and replace " - " with "*" and use text to columns to separate the text from the numbers. However, sometimes the data can be "Sonoma Country - (CA) - 13456-095" at which point this process no longer works. "(CA)" is in B1 & the numbers are in C1.

Is there a formula make sure the 8 digits are always in column B? No text or numbers ever follow the 8 digits which may help solve this problem.

Thanks!
Mike

[AlKey]

Try this

in B1

=SUBSTITUTE(LEFT(A1,FIND("-",A1)+1)," - ","")

In C1

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1,""),"-","",1))

	A	B	C
1	Sonoma Country - 13456-094	Sonoma Country	13456-094
2

[Pete_UK]

If it is always 8 digits (and a hyphen), then you can put this formula in B1:

=TRIM(LEFT(A1,LEN(A1)-9))

and this one in C1:

=RIGHT(A1,9)

Copy both formulae down to the bottom of the data in column A. An easy way to do this is to select cell B1 and double-click the fill handle (the small black square in the bottom right corner of the cursor). Then do the same for C1.

Then select columns B and C and fix the values, i.e. click <copy>, then right-click in that area and select Paste Special, then click Values, then OK then press the <Esc> key. Then you can delete the original column A.

Hope this helps.

Pete

[popipipo]

=TRIM(LEFT(A1,LEN(A1)-12))
=RIGHT(A1,9)

Try these 2 formulas

[chowdown08]

Thanks Pete! Your answer with a few tweaks on my end worked like a charm. Appreciate your and everyone else's help.

[Pete_UK]

Well, that's good to hear - thanks for feeding back.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete