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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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,499

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
105

ADVERTISEMENT

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

 

sandy666

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

Watch MrExcel Video

Forum statistics

Threads
1,133,244
Messages
5,657,582
Members
418,401
Latest member
B_A_M155

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