Formula to extract text before the semi colons = ;

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows
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.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497

ADVERTISEMENT

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
 

arunsjain

Board Regular
Joined
Apr 29, 2016
Messages
108

ADVERTISEMENT

Use power query for splitting text to columns. Refer to link below

 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
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
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,137,351
Messages
5,680,991
Members
419,948
Latest member
Sbakker1

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
Top