Formula to extract text before the semi colons = ;

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,040
Office Version
  1. 2019
Platform
  1. Windows
Hey Excel Gurus,

How are you today?

I have a text in cell that looks something like

New York;Paris;London;Dubai

I want to extract each city in a different cell..
Hence , the city that comes before the first semi colon goes to A1, similarly the city coming before second semi colon and after the first semi colon should be in B2, and so on and so forth.
.

Much appreciation and thanks.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
maybe text to columns with semicolon as delimiter
Hey sandy666,

Thanks for your suggestion.
But that is useful when importing data for the first time. However, the City names are dynamic and always changing due to the fact that these are a product of the concatenate formula.
 
Upvote 0
before the first semi colon goes to A1, similarly the city coming before second semi colon and after the first semi colon should be in B2,

you mean like this?
cts.png
 
Upvote 0
Use power query for splitting text to columns. Refer to link below

 
Upvote 0
I can't help with formula but I can suggest Power Query
after all you can move result table wherever you want, if cities or number of cities will change just click refresh
Column1Column1.1Column1.2Column1.3Column1.4
New York;Paris;London;DubaiNew YorkParisLondonDubai
Karlsruhe;Delhi;lslamabadKarlsruheDelhilslamabad

Code:
// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitCount = Table.AddColumn(Source, "SCount", each List.Count(Text.Split([Column1],";"))),
    MaxCount = List.Max(SplitCount[SCount]),
    Split = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(";"), MaxCount)
in
    Split
 
Upvote 0
Hi, to both!

You can use this:

Libro1
ABCDE
1From thisTo This
2New York;Paris;London;DubaiNew YorkParisLondonDubai
3Karisruhe;Delhi;IslamabadKarisruheDelhiIslamabad 
Hoja1
Cell Formulas
RangeFormula
B2:E3B2=TRIM(MID(SUBSTITUTE(";"&$A2,";",REPT(" ",99)),99*COLUMNS($B2:B2),99))

Blessings!
Thank you Johnmpl

Working as expected. Although I was trying to avoid drag and drop. But your formula is equally good, if not better.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top