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]

If you use Text to Columns with a Space as the Delimiter AND use Text Qualifiers of double-quotes, it will split your line into 8 fields, the erd which be the field you want ("First Word1 Secondword2 thirdword forthword4A").
Is that what you are after?

Are there always the same number of fields (8)?
And is the field you want always the 3rd?
If so, after Text to Columns, you can delete all the other columns so you are just left with that one.
If you turn on your Macro Recorder and perform the Text to Columns, and then the column deletions, you will have the VBA code you need to do this.

 

[hcabs99]

If you use Text to Columns with a Space as the Delimiter AND use Text Qualifiers of double-quotes, it will split your line into 8 fields, the erd which be the field you want ("First Word1 Secondword2 thirdword forthword4A").
Is that what you are after?

Are there always the same number of fields (8)?
And is the field you want always the 3rd?
If so, after Text to Columns, you can delete all the other columns so you are just left with that one.
If you turn on your Macro Recorder and perform the Text to Columns, and then the column deletions, you will have the VBA code you need to do this.

Hi, sorry, i used the quote marks to show the text i wanted to split out, there are not there in the real document. The start point is always the same,the 3rd record but the end point varies as does the number of words & length in the text field.

The only consistent piece is the Start point of the text field and that the next record is always a S , X or F .

 

[Joe4]

Hi, sorry, i used the quote marks to show the text i wanted to split out, there are not there in the real document.

I am sorry, I see that you did mention that in your original post (too early in the morning, time to caffeine up!).
Let me ask this question, are the LENGTHS of the first two fields always the same (is the first always a 4 character code, and the second a 5 character code)?

 

[hcabs99]

I am sorry, I see that you did mention that in your original post (too early in the morning, time to caffeine up!).
Let me ask this question, are the LENGTHS of the first two fields always the same (is the first always a 4 character code, and the second a 5 character code)?

No problem, i wish it was present, would be easy then!

Yes, the first 2 fields are a consistent length

Cheers

Phil

 

[Joe4]

OK, I created my own function in VBA that will do this. Here is the VBA code:

Function GetString(myString As String) As String

    Dim myArray() As String
    Dim i As Long
    Dim myOutput As String
    
    myArray = Split(myString, " ")
    
    For i = 2 To UBound(myArray)
        If (myArray(i) <> "F") And (myArray(i) <> "S") And (myArray(i) <> "X") Then
            myOutput = myOutput & myArray(i) & " "
        Else
            Exit For
        End If
    Next i

    GetString = Trim(myOutput)
    
End Function

So, then you would just use this like any other built-in function in Excel, i.e.
=GetString(A1)

 

[hcabs99]

OK, I created my own function in VBA that will do this. Here is the VBA code:

Function GetString(myString As String) As String

    Dim myArray() As String
    Dim i As Long
    Dim myOutput As String
    
    myArray = Split(myString, " ")
    
    For i = 2 To UBound(myArray)
        If (myArray(i) <> "F") And (myArray(i) <> "S") And (myArray(i) <> "X") Then
            myOutput = myOutput & myArray(i) & " "
        Else
            Exit For
        End If
    Next i

    GetString = Trim(myOutput)
    
End Function

So, then you would just use this like any other built-in function in Excel, i.e.
=GetString(A1)

Excellent many thanks ! , I’ll give this a go this afternoon and let you know how I get on

Cheers

 

[FormR]

the first 2 fields are a consistent length

the next record is always a S , X or F .

Hi, just another option based on the above.

Excel 2013/2016
	A	B
1	C001 19083 First Word1 Secondword2 thirdword forthword4A F 555.000 332.000 223.000 CSE	First Word1 Secondword2 thirdword forthword4A
2	C001 19083 First Word1 Secondword2 thirdword forthword4A more words X 555.000 332.000 223.000 CSE	First Word1 Secondword2 thirdword forthword4A more words
3	C001 59083 oneword S 555.000 332.000 223.000 CSE	oneword
Sheet1

Cell Formulas
Range	Formula
B1	=MID(LEFT(A1,MIN(SEARCH({" S "," X ","F "},A1&" S F X "))-1),12,999)

 

[Joe4]

It looks like FormR came up with a nice, non-VBA solution.
It was the searching for multiple characters at once issue that tripped me up on a strictly formulaic solution, but looks like he figured it out nicely!

 

[hcabs99]

Excellent many thanks ! , I’ll give this a go this afternoon and let you know how I get on

Cheers

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


myArray = Split(myString, " ")

If so , how do I format it please?

And then how do I call the function to update all my data please




Function GetString(myString As String) As String

Dim myArray() As String
Dim i As Long
Dim myOutput As String

myArray = Split(myString, " ")

For i = 2 To UBound(myArray)
If (myArray(i) <> "F") And (myArray(i) <> "S") And (myArray(i) <> "X") Then
myOutput = myOutput & myArray(i) & " "
Else
Exit For
End If
Next i

GetString = Trim(myOutput)

End Function