Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
grab cell text from multi-tab workbook, show text in another workb | Excel Worksheet Functions | |||
Text does not display in "Text boxs" and when wrapping text in a c | Excel Discussion (Misc queries) | |||
Counting a mixed text/number column based on text in another colum | Excel Discussion (Misc queries) | |||
merged cells into one text cell, size varies dependant on text dat | Excel Discussion (Misc queries) | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions |