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, 2022 in Database by gaurav
• 23,260 points
469 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, 2022 in Database by Vaani
• 7,070 points
1,610 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, 2022 in Database by Edureka
• 13,690 points
728 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
2,734 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, 2022 in Database by Edureka
• 13,690 points
2,066 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, 2022 in Database by gaurav
• 23,260 points
2,079 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, 2022 in Database by gaurav
• 23,260 points
8,839 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, 2022 in Database by gaurav
• 23,260 points
598 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, 2022 in Database by gaurav
• 23,260 points
19,940 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, 2022 in Database by gaurav
• 23,260 points
1,054 views
+1 vote
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, 2022 in Database by gaurav
• 23,260 points
158,955 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP