CODEWITHSUNDEEP
excelvbaexcel-formula
PartTimeNerd
PartTimeNerd

Reputation: 305

Split text string in Excel by semicolons and space

Supposedly I have an excel column which looks like this:
enter image description here

How do I achieve this using excel formula:
enter image description here

Right now, I am able to retrieve these using:
enter image description here

=LEFT(D2,SEARCH(";",D2,1)-1) (First)

=MID(D2, SEARCH(";",D2) + 1, SEARCH(";",D2,SEARCH(";",D2)+1)-SEARCH(";",D2)-1) (Second)

=RIGHT(D2,SEARCH(";",D2,1)-1) (Third)

I'd realised that the text was cut off for the RIGHT formula. How can I separate the text using CHAR(10) when each semicolon is found?

Upvotes: 1

Views: 3311

Answers (4)

Kavindu Nilshan
Kavindu Nilshan

Reputation: 811

Got the same problem and used Python code to replace semicolons with commas.

input_file = 'your_csv_file.csv'
output_file = 'converted_csv_file.csv'

with open(input_file, 'r', encoding='utf-8') as infile, open(output_file, 'w', encoding='utf-8') as outfile:
    for line in infile:
        outfile.write(line.replace(';', ','))

Open the output file using Excel. Now it will show in separate columns nicely.

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

You need to replace the semi-colon and space with a semi-colon and new line character.

Use =SUBSTITUTE(A1,"; ", ";" & CHAR(10)) and ensure the cell has text wrapping on.

Upvotes: 3

Teamothy
Teamothy

Reputation: 2016

Or just use

split()

to split your cell value by ";" and

trim()

to cut off additional spaces.

Upvotes: 0

Saeed Joul
Saeed Joul

Reputation: 244

Click on Data from the main menu then click on Text to Columns. Choose the option delimited and tick the semicolon option. Follow the wizard and you'll get your data formatted as requested.

Upvotes: 0

Related Questions