PDA

View Full Version : Solved: text to column question



remy988
05-03-2013, 10:43 AM
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
05-03-2013, 12:14 PM
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 (http://support.microsoft.com/kb/213889) and here (http://www.vbaexpress.com/forum/showthread.php?t=46140)

remy988
05-03-2013, 12:21 PM
hi mdmackillop,

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

thanks again
rem

mdmackillop
05-03-2013, 12:34 PM
The formulae are not dependent upon absolute position.

remy988
05-03-2013, 06:03 PM
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
05-03-2013, 06:28 PM
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
05-04-2013, 03:42 AM
Here's my test

snb
05-04-2013, 04:29 AM
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
05-05-2013, 06:36 AM
thanks everyone for the suggestions. Mike's solution worked the best for me.

thanks again

rem