Remove Text and Numbers from a cell

[jake424]

Hello,

I received a file that has text, numbers, and some more text in a string and I would like to break it out into 3 columns. Each cell contains a name, phone number, and email address, all in the same cell.

Example:
Tony Smith 123-456-7890 tony@smith.com
William Williamson 234-567-8901 x123 william@fakeemail.com
PJ Hin 345-678-9012 PJ@example.com

Unfortunately, the locations of the names, numbers, and emails aren't in the same spot. Is it possible to look this information up by spaces? For name can it look up the info before the second space, then after the 2nd space for number, and after the last space for email?

Thank you so much!

 

[igold]

Hi Jake,

In your example what are you planning to do with Williamson. He would either require 4 columns or the ability to keep phone number and extension in one cell...

igold

 

[István Hirsch]

For the samples provided this works, but char(160) spaces should be substituted with char(32) spaces first.

Excel Workbook
	A	B	C	D
2	Tony Smith 123-456-7890 tony@smith.com	Tony Smith	123-456-7890	tony@smith.com
3	William Williamson 234-567-8901 x123 william@fakeemail.com	William Williamson	234-567-8901 x123	william@fakeemail.com
4	PJ Hin 345-678-9012 PJ@example.com	PJ Hin	345-678-9012	PJ@example.com
Sheet

 

[tonyyy]

If you're looking for something easy peasy... try doing a text to columns, then come back and concatenate the first and last names.

Cheers,

tonyyy

 

[hiker95]

István Hirsch,

Very nicely done.

One for my archives - thanks.

 

[initialize]

If you want to try Vba

Sub seperateInfo()
    
    Dim s As String
    Dim stringA() As String
    Dim lastRow As Long
    
    lastRow = Range("A1").End(xlDown).Row
    
    For i = 2 To lastRow
        s = Cells(i, 1).Value
        stringA = Split(s)
        
        ActiveSheet.Cells(i, 2).Value = stringA(0) + " " + stringA(1)
        ActiveSheet.Cells(i, 3).Value = stringA(2)
        ActiveSheet.Cells(i, 4).Value = stringA(3)
        
    Next i
    
End Sub

Assuming your first row has header. Will do all rows until there is an empty row