How to split text into Columns, Data is with no space in between words and numbers

[satish78]

Hi Friends,

I been trying to split text into columns but did not find a suitable post over here.

Here is the sample file with data extracted into columns
Manually, its taking long human hours to complete.

https://spaces.hightail.com/space/OU0YRqc7Yl

I do not know if it is possible with formula or macro which does exact output.

 

[satish78]

Anyone help me on this?

 

[Peter_SSs]

Anyone help me on this?

You will get many more potential helpers, and faster help, if you explain your problem clearly in words and, if needed, post a small (copyable) screen shot or two directly in your post. My signature block below has help regarding that.

You could try this macro in a copy of your workbook.

A few comments though

1. I assume that your cell C14 is a mistake and it should have been New York, NY

2. I assume that your cell C16 is a mistake and it should be Feasterville-Trevose, PA

3. You would need to provide some logic for excluding the first part of the text in row 19. In all the other rows the 'Industry' goes from the start of the text up to where a lower case letter is followed immediately by an upper case letter. The upshot is that my code returns Accountants To You. Staffing and Recruiting in that row.

Sub Split_Data()
  Dim RX As Object
  Dim a As Variant, b As Variant
  Dim i As Long
  
  Const P1 As String = "^.+?[a-z](?=[A-Z]|\d)"
  Const P2 As String = "[a-z][A-Z].+?(?=[\d, ]*followers)"
  Const P3 As String = "[\d,]+(?= employee)"
  
  Set RX = CreateObject("VBScript.RegExp")
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 3)
  For i = 1 To UBound(a)
    RX.Pattern = P1
    If RX.Test(a(i, 1)) Then b(i, 1) = RX.Execute(a(i, 1))(0)
    RX.Pattern = P2
    If RX.Test(a(i, 1)) Then b(i, 2) = Mid(RX.Execute(a(i, 1))(0), 2)
    RX.Pattern = P3
    If RX.Test(a(i, 1)) Then b(i, 3) = RX.Execute(a(i, 1))(0)
  Next i
  Range("B2:D2").Resize(UBound(b)).Value = b
End Sub