Text Extractions
 

I am trying to extract the characters prior to the "-" but using the left,
mid, or right formula I am required to specify the exact number of
characters. In this case, the "-" is inconsistent on each row. Please help -
I am sure it is something simple but can't seem to find it.

WS1-GREdf-7R22ddd-MWS
WSde-GREa-7R25a-MWSad
WS-GREdfdd-7R32ada-ddddMWS
WSa-GRE-7PC7a-MWSddd

Goal is to easily get WS1 in one column, GREdf in the second, 7R22ddd in the
third, and MWS in the last column. However, each row has more or less
characters prior to each "-".

thanks

Text Extractions
 

Try datatext to columns, delimited and select - as delimiter.
Do it on a test range first to make sure it works the way you want.

Btw, in this case it was easy but I don't understand why you would expect
that. Normally
Excel is very hard to use for text string parsing


--


Regards,


Peo Sjoblom


"Nikki" wrote in message
...
I am trying to extract the characters prior to the "-" but using the left,
mid, or right formula I am required to specify the exact number of
characters. In this case, the "-" is inconsistent on each row. Please
help -
I am sure it is something simple but can't seem to find it.

WS1-GREdf-7R22ddd-MWS
WSde-GREa-7R25a-MWSad
WS-GREdfdd-7R32ada-ddddMWS
WSa-GRE-7PC7a-MWSddd

Goal is to easily get WS1 in one column, GREdf in the second, 7R22ddd in
the
third, and MWS in the last column. However, each row has more or less
characters prior to each "-".

thanks

Text Extractions
 

Hello Nikki,
Assuming data stars on row 6 and column B

1st column (C)
=left(b6,find("-",b6,1)-1)

2nd column (D)
=MID(B6,LEN(C6)+2,FIND("-",B6,LEN(C6)+2)-LEN(C6)-2)

3rd column (E)
=MID(B6,LEN(C6)+LEN(D6)+3,FIND("-",B6,(LEN(C6)+LEN(D6)+3))-(LEN(C6)+LEN(D6)+3))

4th column (F)
=RIGHT(B6,LEN(B6)-(LEN(C6)+LEN(D6)+LEN(E6)+3))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Nikki" escreveu:

I am trying to extract the characters prior to the "-" but using the left,
mid, or right formula I am required to specify the exact number of
characters. In this case, the "-" is inconsistent on each row. Please help -
I am sure it is something simple but can't seem to find it.

WS1-GREdf-7R22ddd-MWS
WSde-GREa-7R25a-MWSad
WS-GREdfdd-7R32ada-ddddMWS
WSa-GRE-7PC7a-MWSddd

Goal is to easily get WS1 in one column, GREdf in the second, 7R22ddd in the
third, and MWS in the last column. However, each row has more or less
characters prior to each "-".

thanks

Text Extractions
 

If you are willing to entertain a macro solution...

Sub SplitOnDashes()
Dim X As Long
Dim C As Range
Dim S() As String
For Each C In Intersect(Selection, ActiveSheet.UsedRange)
S = Split(C.Value, "-")
For X = 0 To UBound(S)
C.Offset(0, X).Value = S(X)
Next
Next
End Sub

Rick


"Nikki" wrote in message
...
I am trying to extract the characters prior to the "-" but using the left,
mid, or right formula I am required to specify the exact number of
characters. In this case, the "-" is inconsistent on each row. Please
help -
I am sure it is something simple but can't seem to find it.

WS1-GREdf-7R22ddd-MWS
WSde-GREa-7R25a-MWSad
WS-GREdfdd-7R32ada-ddddMWS
WSa-GRE-7PC7a-MWSddd

Goal is to easily get WS1 in one column, GREdf in the second, 7R22ddd in
the
third, and MWS in the last column. However, each row has more or less
characters prior to each "-".

thanks