Trying to extract variable length text from cells.

[Davebro]

Trying to extract the two text names to columns B & C

2.00 Space Tourist 4.50 Cash The Cheque
2.38 Arctic Ambition 4.00 Sheephaven Flyer
2.50 Ocean Baroque 4.20 King Cuan
3.00 Snowcapped 4.33 Impact Warrior
3.50 Rathnaleen Kal 6.00 Atimetodream
2.10 Mashadi 4.50 Succession
2.88 Veil Of Shadows 5.50 Araminta
2.00 Dream Composer 5.00 Lihou

 

[arthurbr]

Data -Text to columns - Use space as delimiter and in the last window of the wizard select "Do not import" irrelevant columns

 

[Georgiboy]

Are you still using Excel 2016?

 

[Davebro]

Thank you for your eply but this seems to spread the text to column F, does not seperate the names.

 

[Joe4]

Thank you for your eply but this seems to spread the text to column F, does not seperate the names.

Can you show us the expected results of your original data?
We want to be sure that we fully understand what parts of the strings are the "names" that you are trying to pull (or is it just everything in-between/after the numbers)?

 

[Davebro]

Are you still using Excel 2016?

Yes

 

[Davebro]

Can you show us the expected results of your original data?
We want to be sure that we fully understand what parts of the strings are the "names" that you are trying to pull (or is it just everything in-between/after the numbers)?

2.00 Space Tourist 4.50 Cash The Cheque	Space Tourist	Cash The Cheque
2.38 Arctic Ambition 4.00 Sheephaven Flyer
2.50 Ocean Baroque 4.20 King Cuan
3.00 Snowcapped 4.33 Impact Warrior
3.50 Rathnaleen Kal 6.00 Atimetodream
2.10 Mashadi 4.50 Succession
2.88 Veil Of Shadows 5.50 Araminta
2.00 Dream Composer 5.00 Lihou

 

[Joe4]

If my assumption is correct, and every number has a "." in it, then you can create your own function in VBA to do this.
I started with some code I borrowed from here: How to remove / split text and numbers in Excel cell
and came up with this code:

Function RemoveNumbers(str As String, num As Byte) As String
'   Started with code borrowed from here: https://www.ablebits.com/office-addins-blog/remove-text-numbers-from-string-excel/
'       "str" is the string you want to parse
'       "num" is the number of the name you want to return
       
    Dim temp As String
    Dim arr() As String

'   Remove numbers
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[0-9]"
        temp = .Replace(str, "")
    End With
   
'   Split string on period
    arr = Split(temp, ".")
   
'   Return designated word and trim leading/trailing spaces
    RemoveNumbers = Trim(arr(num))
   
End Function

All you need to do is to insert a new module in the VB Editor for this workbook, and copy and paste that code there.
Then you can use it like any other function in your workbook, i.e. to return the first name from an entry in cell A2, you would use:

=RemoveNumbers(A2,1)

To return the second name, you would use:

=RemoveNumbers(A2,2)

So based on your data, here is what the results would look like:

 

[Davebro]

If my assumption is correct, and every number has a "." in it, then you can create your own function in VBA to do this.
I started with some code I borrowed from here: How to remove / split text and numbers in Excel cell
and came up with this code:

Function RemoveNumbers(str As String, num As Byte) As String
'   Started with code borrowed from here: https://www.ablebits.com/office-addins-blog/remove-text-numbers-from-string-excel/
'       "str" is the string you want to parse
'       "num" is the number of the name you want to return
     
    Dim temp As String
    Dim arr() As String

'   Remove numbers
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[0-9]"
        temp = .Replace(str, "")
    End With
 
'   Split string on period
    arr = Split(temp, ".")
 
'   Return designated word and trim leading/trailing spaces
    RemoveNumbers = Trim(arr(num))
 
End Function

All you need to do is to insert a new module in the VB Editor for this workbook, and copy and paste that code there.
Then you can use it like any other function in your workbook, i.e. to return the first name from an entry in cell A2, you would use:

=RemoveNumbers(A2,1)

To return the second name, you would use:

=RemoveNumbers(A2,2)

So based on your data, here is what the results would look like:
View attachment 92378

That's terrific, many thanks for your help.

 

[Joe4]

You are welcome.
Glad we were able to help!