Seperating text from one cell into multiple columns

[tandkb]

Hi, I am trying to seperate text that is all in one cell into multiple columns. See screenshot below. Name, Business Number, Address and Hours into seperate columns. TIA!

 

[alansidman]

Please upload same data using XL2BB. Cannot manipulate data in a picture.

 

[DanteAmor]

You've already tried excel Text To Columns functionality

 

[tandkb]

ATM List 4.19.21.xlsx
	A
5	Express Mart & Tobacco Business phone:Show number →252-315-4729IMPORTANT! Address:1327 Ward BlvdWilson, NC 27893USA Open hours:Sun-Sat: 7:00 am – 11:00 pm
6	Cenex Gas & Store Business phone:Show number →+13202319708IMPORTANT! Address:6647 Highway 71 NEWillmar, MN 56201USA Open hours:Mon-Sun: 6:00 am – 10:00 pm
7	Everyday Conoco Business phone:Show number →303-651-3694IMPORTANT! Address:900 Ken Pratt BlvdLongmont, CO 80501USA Open hours:Sun-Sat: 6:00 am – 10:00 pm
8	Fast Stop Food Store Business phone:Show number →916-666-5613IMPORTANT! Address:750 46th StSacramento, CA 95819USA Open hours:Sun-Sat: 7:00 am – 12:00 am
9	Covington Mart Business phone:Show number →678-949-9860IMPORTANT! Address:3445 Covington DrDecauter, GA 30032USA Open hours:Mon-Sun: 9:00 am “ 10:00 pm
10	Nicky D"s Burgerz Business phone:Show number →8109375446IMPORTANT! Address:1209 Military StPort Huron, MI 48060USA Open hours:Mon-Sun: 7:00 am – 10:00 pm
Sheet4

 

[tandkb]

You've already tried excel Text To Columns functionality

View attachment 62798

i tried but couldnt get it to work.

 

[DanteAmor]

Review this:

Using the Text To Column Functionality of Excel

Do you know about the Excel Text to Column Functionality? The Text to Column functionality is one of the amazing functionality provided by Excel which allows yo

excelunlocked.com

Check columns B onwards:

varios 19abr2022.xlsm
	A	B	C	D	E
1	Express Mart & Tobacco Business phone:Show number →252-315-4729IMPORTANT! Address:1327 Ward BlvdWilson, NC 27893USA Open hours:Sun-Sat: 7:00 am – 11:00 pm	Express Mart & Tobacco	Business phone:Show number →252-315-4729IMPORTANT!	Address:1327 Ward BlvdWilson, NC 27893USA	Open hours:Sun-Sat: 7:00 am – 11:00 pm
2	Cenex Gas & Store Business phone:Show number →+13202319708IMPORTANT! Address:6647 Highway 71 NEWillmar, MN 56201USA Open hours:Mon-Sun: 6:00 am – 10:00 pm	Cenex Gas & Store	Business phone:Show number →+13202319708IMPORTANT!	Address:6647 Highway 71 NEWillmar, MN 56201USA	Open hours:Mon-Sun: 6:00 am – 10:00 pm
3	Everyday Conoco Business phone:Show number →303-651-3694IMPORTANT! Address:900 Ken Pratt BlvdLongmont, CO 80501USA Open hours:Sun-Sat: 6:00 am – 10:00 pm	Everyday Conoco	Business phone:Show number →303-651-3694IMPORTANT!	Address:900 Ken Pratt BlvdLongmont, CO 80501USA	Open hours:Sun-Sat: 6:00 am – 10:00 pm
4	Fast Stop Food Store Business phone:Show number →916-666-5613IMPORTANT! Address:750 46th StSacramento, CA 95819USA Open hours:Sun-Sat: 7:00 am – 12:00 am	Fast Stop Food Store	Business phone:Show number →916-666-5613IMPORTANT!	Address:750 46th StSacramento, CA 95819USA	Open hours:Sun-Sat: 7:00 am – 12:00 am
5	Covington Mart Business phone:Show number →678-949-9860IMPORTANT! Address:3445 Covington DrDecauter, GA 30032USA Open hours:Mon-Sun: 9:00 am “ 10:00 pm	Covington Mart	Business phone:Show number →678-949-9860IMPORTANT!	Address:3445 Covington DrDecauter, GA 30032USA	Open hours:Mon-Sun: 9:00 am “ 10:00 pm
6	Nicky D"s Burgerz Business phone:Show number →8109375446IMPORTANT! Address:1209 Military StPort Huron, MI 48060USA Open hours:Mon-Sun: 7:00 am – 10:00 pm	Nicky D"s Burgerz	Business phone:Show number →8109375446IMPORTANT!	Address:1209 Military StPort Huron, MI 48060USA	Open hours:Mon-Sun: 7:00 am – 10:00 pm
Hoja5

 

[tandkb]

Review this:

Using the Text To Column Functionality of Excel

Do you know about the Excel Text to Column Functionality? The Text to Column functionality is one of the amazing functionality provided by Excel which allows yo

excelunlocked.com

Check columns B onwards:

varios 19abr2022.xlsm
	A	B	C	D	E
1	Express Mart & Tobacco Business phone:Show number →252-315-4729IMPORTANT! Address:1327 Ward BlvdWilson, NC 27893USA Open hours:Sun-Sat: 7:00 am – 11:00 pm	Express Mart & Tobacco	Business phone:Show number →252-315-4729IMPORTANT!	Address:1327 Ward BlvdWilson, NC 27893USA	Open hours:Sun-Sat: 7:00 am – 11:00 pm
2	Cenex Gas & Store Business phone:Show number →+13202319708IMPORTANT! Address:6647 Highway 71 NEWillmar, MN 56201USA Open hours:Mon-Sun: 6:00 am – 10:00 pm	Cenex Gas & Store	Business phone:Show number →+13202319708IMPORTANT!	Address:6647 Highway 71 NEWillmar, MN 56201USA	Open hours:Mon-Sun: 6:00 am – 10:00 pm
3	Everyday Conoco Business phone:Show number →303-651-3694IMPORTANT! Address:900 Ken Pratt BlvdLongmont, CO 80501USA Open hours:Sun-Sat: 6:00 am – 10:00 pm	Everyday Conoco	Business phone:Show number →303-651-3694IMPORTANT!	Address:900 Ken Pratt BlvdLongmont, CO 80501USA	Open hours:Sun-Sat: 6:00 am – 10:00 pm
4	Fast Stop Food Store Business phone:Show number →916-666-5613IMPORTANT! Address:750 46th StSacramento, CA 95819USA Open hours:Sun-Sat: 7:00 am – 12:00 am	Fast Stop Food Store	Business phone:Show number →916-666-5613IMPORTANT!	Address:750 46th StSacramento, CA 95819USA	Open hours:Sun-Sat: 7:00 am – 12:00 am
5	Covington Mart Business phone:Show number →678-949-9860IMPORTANT! Address:3445 Covington DrDecauter, GA 30032USA Open hours:Mon-Sun: 9:00 am “ 10:00 pm	Covington Mart	Business phone:Show number →678-949-9860IMPORTANT!	Address:3445 Covington DrDecauter, GA 30032USA	Open hours:Mon-Sun: 9:00 am “ 10:00 pm
6	Nicky D"s Burgerz Business phone:Show number →8109375446IMPORTANT! Address:1209 Military StPort Huron, MI 48060USA Open hours:Mon-Sun: 7:00 am – 10:00 pm	Nicky D"s Burgerz	Business phone:Show number →8109375446IMPORTANT!	Address:1209 Military StPort Huron, MI 48060USA	Open hours:Mon-Sun: 7:00 am – 10:00 pm
Hoja5

Thanks that is exactly what I am looking for but still dont know how to do it. I am not familiar with the XLBB 2.0 and how it works exactly.

 

[DanteAmor]

forget the XL2BB tool, it was just my example.

You should read what I put you in the link

Using the Text To Column Functionality of Excel

Do you know about the Excel Text to Column Functionality? The Text to Column functionality is one of the amazing functionality provided by Excel which allows yo

excelunlocked.com

in this part:

How to use “Fixed Width” Functionality

- Select the cell range (A5:A10). And open the “Convert Text to Column Wizard”
- In the first step of this wizard, select the radio button “Fixed Width”
- Now click on the “Finish” button.

 

[Peter_SSs]

Assuming that there are more than the 6 rows of data that we have seen, it may be possible that Text to Columns - Fixed Width will not work for you. That will depend on the lengths of the various texts and the number of spaces between them.
Even if TTC- FW does "work" for you, it will leave all the data with multiple spaces before/after the remaining text values. That would be no good if you are using the resulting values to do look-ups or are printing labels from them etc. In that case you may then need to use a formula or macro to "clean up" those extra space characters.

Alternatively you could try this macro (with a copy of your data as it over-writes the original data) which should both split the text and remove the leading/trailing spaces
This particular code assumes there are no semi-colons in the existing data.

Sub Split_Text()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(\s{2,})(Business|Address|Open|$)"
  With Range("A5", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = RX.Replace(a(i, 1), ";$2")
    Next i
    Application.ScreenUpdating = False
    .Value = a
    .TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False
    .Resize(, 4).Columns.AutoFit
    Application.ScreenUpdating = True
  End With
End Sub

Using your sample data from post #4, this is my result after running the above code.

tandkb.xlsm
	A	B	C	D
5	Express Mart & Tobacco	Business phone:Show number →252-315-4729IMPORTANT!	Address:1327 Ward BlvdWilson, NC 27893USA	Open hours:Sun-Sat: 7:00 am – 11:00 pm
6	Cenex Gas & Store	Business phone:Show number →+13202319708IMPORTANT!	Address:6647 Highway 71 NEWillmar, MN 56201USA	Open hours:Mon-Sun: 6:00 am – 10:00 pm
7	Everyday Conoco	Business phone:Show number →303-651-3694IMPORTANT!	Address:900 Ken Pratt BlvdLongmont, CO 80501USA	Open hours:Sun-Sat: 6:00 am – 10:00 pm
8	Fast Stop Food Store	Business phone:Show number →916-666-5613IMPORTANT!	Address:750 46th StSacramento, CA 95819USA	Open hours:Sun-Sat: 7:00 am – 12:00 am
9	Covington Mart	Business phone:Show number →678-949-9860IMPORTANT!	Address:3445 Covington DrDecauter, GA 30032USA	Open hours:Mon-Sun: 9:00 am “ 10:00 pm
10	Nicky D"s Burgerz	Business phone:Show number →8109375446IMPORTANT!	Address:1209 Military StPort Huron, MI 48060USA	Open hours:Mon-Sun: 7:00 am – 10:00 pm
Sheet3