[SOLVED] Splitting cell text strings at 4-digit number and at space after # symbol

First poster solved it, thank you both for the help!


----------------------
original post:

Here is an example of one cell out of thousands like it I'm trying to change:

JASON KIPNIS 2013 Topps #267 Indians


And here is what I'd like it to be:

2013 Topps #267 JASON KIPNIS Indians



I'm dealing with baseball cards, if you didn't know, and the years, brands, #s, players, and teams are all different.


So what I think I need to do is split the cell at the first instance of a 4-digit number (the year), then again at the first space after the '#' symbol. This would hopefully leave me with 3 columns:

JASON KIPNIS / 2013 Topps #267 / Indians


At which point I can use the '&' formula to arrange the cells in the correct order in a new single cell.


Can someone please tell me the formulas I should use to achieve this? Thank you very much!

hi portokie. the 3 formulas you can try:
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-2)

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),FIND(" ",A1,FIND("#",A1)+1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))

=MID(A1,FIND(" ",A1,FIND("#",A1)+1)+1,LEN(A1))

use TRIM if needed. that's to remove spaces before, after & more than 1 space in between

Hi,

Or try these three formulas:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)*3,LEN(A1)*2))

=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)*3))

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

I think I would use Data>Text to Columns to separate the string and then use a simple concatentation formula to reassemble in the order you want.

For example:

A2 = JASON KIPNIS 2013 Topps #267 Indians

Select A2
Goto Data>Text to Columns
Select: Delimited
Click: Next
Select: Space
Click: Finish

The string will be separated like this:

A2: JASON
B2: KIPNIS
C2: 2013
D2: Topps
E2: #267
F2: Indians

Then, use this formula to reassemble in the order you want:

=C2&" "&D2&" "&E2&" "&A2&" "&B2&" "&F2

Then, you could inspect the results to make sure you got what you wanted.

Select the cell with the formula
Right click>Copy
Right click>Paste Special>Values
OK

Then, delete the original data in A2:F2