How to split a string of text in excel based on a given word

0 votes

I have a list of combinations of companies, cities, and states in excel, each a string. I would like to split the string of words based on a given word (the city name) and the result to be two columns, one with with company name, one with the city and state.

Splitting on space or symbol delimiters doesn't work because the companies don't all have one word names, and similar for cities.

I have thousands of records and would like to loop this as well. I've tried the SPLIT() function in VBA but not sure how to loop it.

Initial                     Splitting word  Result 1     Result 2
Clean Choc Detroit MI       Detroit         Clean Choc  Detroit MI
Space Kites Des Moines IA   Des Moines      Space Kites Des Moines IA
Tattoosie Chicago IL        Chicago         Tattoosie   Chicago IL
One for Two New York City NYNew York City   One for Two New York City NY
Limonistas Carlsbad CA      Carlsbad        Limonistas  Carlsbad CA

Apr 4 in Database by Edureka
• 8,820 points
40 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Database

0 votes
1 answer

How to calculate age (in years) based on Date of Birth and getDate()

Following can be a solution to your ...READ MORE

answered Feb 23 in Database by Vaani
• 5,040 points
35 views
0 votes
1 answer

How to enter a series of numbers automatically in Excel

Excel, unlike other Microsoft Office programmes, does ...READ MORE

answered Apr 4 in Database by Edureka
• 9,320 points
20 views
0 votes
1 answer

How to check if a table exists in a given schema

Since you havent mentioned the DBMS in ...READ MORE

answered Oct 4, 2019 in Database by Daric
• 500 points
1,847 views
0 votes
0 answers

How to get address, Column Name and Row Name of all marked rows in Excel table as rows in new worksheet

 need the row/column combinations marked with an ...READ MORE

Feb 24 in Database by Edureka
• 9,320 points
28 views
0 votes
1 answer

[Excel][VBA] How to draw a line in a graph?

Sub MakeChart() Dim x(20) ...READ MORE

answered Mar 24 in Database by Edureka
• 8,820 points
26 views
0 votes
1 answer

How can I calculate deciles with a range of 12,000 cells in excel?

1. Enter the following formula in cell ...READ MORE

answered Mar 25 in Database by Edureka
• 8,820 points
53 views
0 votes
1 answer

How to add a named sheet at the end of all Excel sheets?

Kindly use this one liner: Sheets.Add(After:=Sheets(Sheets.Count)).Name = "new_sheet_name" Or ...READ MORE

answered Apr 5 in Database by Edureka
• 8,820 points
25 views
0 votes
1 answer

How do I split a cell in Excel into two or more where they are divided horizontally?

Table cells should be combined. To make a ...READ MORE

answered Apr 6 in Database by Edureka
• 8,820 points
26 views
0 votes
1 answer

How to run a SQL query on an Excel table?

On Excel tables, how to construct and ...READ MORE

answered Apr 11 in Database by Edureka
• 8,820 points
25 views
0 votes
1 answer

how to change format of date from mm/dd/yyyy to dd-mmm-yyyy in MS Excel

First, pick the cells that contain dates, ...READ MORE

answered Apr 11 in Database by Edureka
• 8,820 points
28 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP