Solved: text to column question

[remy988]

hi,
i have data in column A that contains a 14 character string that may have 1 or 2 dashes (-) or none at all. i want to be able to separate the data into 2 columns using the last dash on the right.
so if
A1 = ABCD-EFG-HIJ, then
B1 will be ABCD-EFG &
C1 will be HIJ.


if A2 = ABCDEF-GHI then
B2 will be ABCDEF &
C2 will be GHI

what formula can i use or can i do this with code?

thanks
rem

[mdmackillop]

In B1 =IF(SUM(LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))=2,LEFT(A1,LEN(A1)-LEN(C1)-1),TRIM(RIGHT(SUBSTITUTE(TRIM(A1),"-",REPT(" ",50)),50)))

In C1 =IF(SUM(LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))=2,TRIM(RIGHT(SUBSTITUTE(TRIM(A1),"-",REPT(" ",50)),50)),"")

More info here and here

[remy988]

hi mdmackillop,

the dashes are not always in the same position; how can i adjust the formula?

thanks again
rem

[mdmackillop]

The formulae are not dependent upon absolute position.

[remy988]

hi,
i can't get this to work correctly.
if there is only one dash, then the cell in column B will contain the data that's after the dash and the cell in column C is left blank.

rem

[mikerickson]

How about
in B1 =LEFT(A1, LEN(A1)-LEN(C1)-1)

and in C1
=TRIM(RIGHT(SUBSTITUTE(REPT("-",2-MIN(2,(LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))))&A1,"-",REPT(" ","255"),2),255))

[mdmackillop]

Here's my test

[snb]

I used the array formulae:

In B1
=LEFT(A1;MAX((MID(A1;ROW($1:$100);1)="-")*ROW($1:$100))-1)

In C1
=MID(A1;MAX((MID(A1;ROW($1:$100);1)="-")*ROW($1:$100))+1;100)

[remy988]

thanks everyone for the suggestions. Mike's solution worked the best for me.

thanks again

rem