Text to columns , using any letter

[hcabs99]

HI

I have a file to import, which contains a number of columns, one of which is a text field containing a varying number of words and field length. Is there a way via Text to columns functionality or via VBA where i can look for the end of the text and put the break in there?

Example below : The items in the quotations are the area i need to split out (sadly no quotations in the source file!) The text field will always end in a letter.


C001 19083 "First Word1 Secondword2 thirdword forthword4A" F 555.000 332.000 223.000 CSE

If it helps.. The next field after the text will always be an F , X or an S

Hope this makes sense. Appreciate any advise on this

Cheers

 

[Joe4]

Sorry, I'm a little confused, do I need to store my data range in this value here?

No, not at all.
You should not need to change a single thing in the code. We created a function, like other Excel functions (i.e. SUM, LEFT, IF, etc).
All you need to do is copy and paste the Function VBA code into a General Module in the VB Editor, and that is it! You shouldn't need to access or change that code again.

You feed in the string or cell reference when calling the function, like you would with any other function.
So if your entry was in cell A1, then you just enter this formula in (or wherever you want):
=GetString(A1)

 

[hcabs99]

No, not at all.
You should not need to change a single thing in the code. We created a function, like other Excel functions (i.e. SUM, LEFT, IF, etc).
All you need to do is copy and paste the Function VBA code into a General Module in the VB Editor, and that is it! You shouldn't need to access or change that code again.

You feed in the string or cell reference when calling the function, like you would with any other function.
So if your entry was in cell A1, then you just enter this formula in (or wherever you want):
=GetString(A1)

Ok, I see, so I've done that , and put a line of the string into cell A27

C001 409990 TWIST 500G 1 BULK F 31.000 9.000 22.000 CSE

Then put this into another cell =getstring(A27)

But I get a #Value ! and it tells me "a value used in the formula is of the wrong data type" when hovering over the error

What am I doing wrong please?

 

[Joe4]

It returns "TWIST 500G 1 BULK" for me.

Where did this data come from?
Do you have any special characters in it? I have seens lots of data that comes from the Web or other programs using "special spaces" which mess things up (they look like regular spaces, but are not).

Did you try FormR's formulaic solution?
If that one also returns errors, then I definitely would say that there is an issue with the data.

 

[Rick Rothstein]

Worksheet Formulas Cell Formula B1 =MID(LEFT(A1,MIN(SEARCH({" S "," X ","F "},A1&" S F X "))-1),12,999) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

It looks like you are missing a leading space in front of the "F ". Without it, the split out text will have a trailing blank space when the split out character is the isolated "F".

By the way, assuming NONE of the words being split out consist of a single character, you can shorten your formula up by using a wildcard instead...

=MID(LEFT(A1,MIN(SEARCH(" ? ",A1&"   "))-1),12,999)

Note: There are 3 spaces in the text being concatenated onto the A1.

 

[Rick Rothstein]

Ok, I see, so I've done that , and put a line of the string into cell A27

C001 409990 TWIST 500G 1 BULK F 31.000 9.000 22.000 CSE

In Message #5 you said, "Yes, the first 2 fields are a consistent length"; however, the second field above has 6 characters in it whereas your original example had 5 characters in its second field. That would mean your first two fields are not a consistent length. Can you please clarify that for us?

 

[hcabs99]

It returns "TWIST 500G 1 BULK" for me.

Where did this data come from?
Do you have any special characters in it? I have seens lots of data that comes from the Web or other programs using "special spaces" which mess things up (they look like regular spaces, but are not).

Did you try FormR's formulaic solution?
If that one also returns errors, then I definitely would say that there is an issue with the data.

It’s come from a text file pasted in .. FormR solution works but I don’t think I’ve explained my desired end result properly ( my apologies ) . The data in actually interested in is not so much the text but the data after it , i wanted to find a solution via text to columns so it could consistently find the start point of the data after the text which I can then do a simple text to columns using space as the delimiter
Does that make sense ? I think I can adapt FormR ‘s formala to do that but Im still trying to understand how it works ! Appreciate both of your support on this !

 

[hcabs99]

In Message #5 you said, "Yes, the first 2 fields are a consistent length"; however, the second field above has 6 characters in it whereas your original example had 5 characters in its second field. That would mean your first two fields are not a consistent length. Can you please clarify that for us?

Yes , dam ok , didn’t clock that .. actually I can get round that via a text to column using fixed width as i think it’s right justified .. so if easier assume the first part of the string is the first letter of the text .. sorry for the confusion

 

[Joe4]

I am sure that someone can probably come up with shorter or more efficient code, but this should work. If all your data is in column A, it will do a Text to Columns on column A in the manner that you want:

Sub MySplitData()

    Dim lRow As Long
    Dim r As Long
    Dim myString As String
    Dim ln As Long
    Dim i As Long
    Dim s As Long
    Dim temp As String

    Application.ScreenUpdating = False

'   Find last row of data in column A
    lRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows starting with row 1
    For r = 1 To lRow
'       Initial space counter to 0
        s = 0
'       Capture entry in column A
        myString = Cells(r, "A")
        ln = Len(myString)
'       Find second space
        For i = 1 To ln
'           Increment space counter
            If Mid(myString, i, 1) = " " Then
                s = s + 1
            End If
'           When second space, insert double-quote
            If s = 2 Then
                myString = Left(myString, i) & Chr(34) & Mid(myString, i + 1)
                Exit For
            End If
        Next i
'       Insert second double-quote
        temp = myString & " F S X "
        temp = Replace(temp, " F ", Chr(34) & " F ")
        temp = Replace(temp, " S ", Chr(34) & " S ")
        temp = Replace(temp, " X ", Chr(34) & " X ")
        myString = Left(temp, ln + 2)
        Cells(r, "A") = myString
    Next r
    
'   Do text to columns on data
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1)), _
        TrailingMinusNumbers:=True
          
    Application.ScreenUpdating = True
    
End Sub

 

[Rick Rothstein]

Taking the cell reference from Message #12 , assuming your first text string is in cell A27, put this formula in cell B27 and copy it across for as many cells as you think you might ever have split-out data for, then copy all of those formulas down to the bottom of your data...

=TRIM(MID(SUBSTITUTE(MID($A27,MIN(SEARCH({" S "," X "," F "},$A27&" S F X "))+2,200)," ",REPT(" ",200)),COLUMNS($B:B)*200,200))

 

[Rick Rothstein]

I am sure that someone can probably come up with shorter or more efficient code, but this should work. If all your data is in column A, it will do a Text to Columns on column A in the manner that you want:

Sub MySplitData()

    Dim lRow As Long
    Dim r As Long
    Dim myString As String
    Dim ln As Long
    Dim i As Long
    Dim s As Long
    Dim temp As String

    Application.ScreenUpdating = False

'   Find last row of data in column A
    lRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows starting with row 1
    For r = 1 To lRow
'       Initial space counter to 0
        s = 0
'       Capture entry in column A
        myString = Cells(r, "A")
        ln = Len(myString)
'       Find second space
        For i = 1 To ln
'           Increment space counter
            If Mid(myString, i, 1) = " " Then
                s = s + 1
            End If
'           When second space, insert double-quote
            If s = 2 Then
                myString = Left(myString, i) & Chr(34) & Mid(myString, i + 1)
                Exit For
            End If
        Next i
'       Insert second double-quote
        temp = myString & " F S X "
        temp = Replace(temp, " F ", Chr(34) & " F ")
        temp = Replace(temp, " S ", Chr(34) & " S ")
        temp = Replace(temp, " X ", Chr(34) & " X ")
        myString = Left(temp, ln + 2)
        Cells(r, "A") = myString
    Next r
    
'   Do text to columns on data
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1)), _
        TrailingMinusNumbers:=True
          
    Application.ScreenUpdating = True
    
End Sub

If you are correct in that being what the OP wants (I am still unclear as to what the OP actually wants for his output, hence what I posted in Message #19 ), then this somewhat shorter macro seems to produce the same output as your above code does...

[table="width: 500"]
[tr]
	[td]Sub MySplitData()
  Dim R As Long, TxtStart As Long, CellTxt As String, Txt As String, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    CellTxt = Data(R, 1)
    Txt = Evaluate("MID(LEFT(A" & R & ",MIN(SEARCH({"" S "","" X "","" F ""},A" & R & "&"" S F X ""))-1),12,999)")
    TxtStart = InStr(CellTxt, Txt)
    CellTxt = Replace(CellTxt, " ", "|")
    Mid(CellTxt, TxtStart) = Txt
    Data(R, 1) = CellTxt
  Next
  Range("A1").Resize(UBound(Data)) = Data
  Application.DisplayAlerts = False
  Columns("A").TextToColumns [A1], xlDelimited, , , False, False, False, False, True, "|"
  Application.DisplayAlerts = True
End Sub[/td]
[/tr]
[/table]