Complex String Operations without using Text to Columns

[excelsion]

Hi,

I have a data source that contains the names of companies and is stored on one of the core applications used in my company.when I copy that information on Excel, it comes in the following format:

Amazon UK Ltd Google Inc British Gas Pallermo Ltd

Basically, there are a lot of spaces between two company names ( approx. 25 to 30 spaces). I cant use text to columns as it will separate all the texts that have a space between them. I want the final output to be :

Amazon UK Ltd
Google Inc
British Gas
Pallermo Ltd

I am not familiar with VB scripting so I wont be able to use that. IS there a solution to this that anyone can recommend?

Thanks

 

[Chihiro]

Is there only single space (or less than between company names) between first part and second part of company name? If so you can use SUBSTITUTE like below.

Assuming string is copied into A1.
In B1
=SUBSTITUTE(A1," ",";")

Copy result and paste as value. Then use text to column using ";" as delimiter and check "Treat consecutive delimiters as one".

 

[Chihiro]

Note: Between double quote, enter about 5 spaces or as many as needed.

 

[excelsion]

Note: Between double quote, enter about 5 spaces or as many as needed.

The Company names are separate wide apart by spaces. Hence, I am unable to separate them with your solution.

For Example, After Amazon Uk, there will be 20 - 30 spaces followed by the name of the next company.Then another 20-30 spaces followed by the name of the another company. However , the number of spaces between the words of the same company is always 1. i.e between Amazon and UK..

 

[mikerickson]

what you can do is use
=SUBSTITUTE(A1, " ","~") {there is a double space in the second argument}

To convert all you double spaces into ~, but leave the single spaces (e.g. between Amazon UK and Ltd) in place.

Then TextToColumns on ~ will split the different fields appropriately.

You may need to use TRIM to clean leading/trailing spaces off of the result.

 

[Rick Rothstein]

Assuming your data is in Column A starting on Row 1, here is a macro you can use to split the company names out into adjacent columns...

[table="width: 500"]
[tr]
	[td]Sub CompanyNames()
  Dim R As Long, vNum As Variant, Data As Variant
  Data = Range("[B][COLOR="#0000FF"]A1[/COLOR][/B]", Cells(Rows.Count, "[B][COLOR="#0000FF"]A[/COLOR][/B]").End(xlUp))
  For R = 1 To UBound(Data)
    Data(R, 1) = Replace(Data(R, 1), Space(2), Chr(1))
    Data(R, 1) = Replace(Data(R, 1), Chr(1) & " ", Chr(1))
  Next
  Range("[B][COLOR="#800080"]B[/COLOR][/B][B][COLOR="#0000FF"]1[/COLOR][/B]").Resize(UBound(Data)) = Data
  Columns("[B][COLOR="#800080"]B[/COLOR][/B]").TextToColumns , xlDelimited, , True, False, False, False, False, True, Chr(1)
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (CompanyNames) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Rick Rothstein]

what you can do is use
=SUBSTITUTE(A1, " ","~") {there is a double space in the second argument}

To convert all you double spaces into ~, but leave the single spaces (e.g. between Amazon UK and Ltd) in place.

Then TextToColumns on ~ will split the different fields appropriately.

You may need to use TRIM to clean leading/trailing spaces off of the result.

Instead of having to do the step highlighted in red, just do one more substitution...

=SUBSTITUTE(SUBSTITUTE(A1, REPT(" ",2),"~"),"~ ","~")

Note I replaced the double space with a REPT function call to avoid this forum's problem displaying multiple spaces. This is basically the same method I used in my macro in Message #6.