MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

String Handling in Excel

  Asked By: Gertrude    Date: Aug 19    Category: MS Office    Views: 2067

I have a number of mainframe dumps to Excel spreadsheets.

We asked for the same data from different financial institutions but
we get it in all kinds of forms and columns. I have been combining
string handling formulas and Text to Column features in progressive
steps to handle one of the worst ...

In one cell, I get:

John Doe 123 Main Street Calgary AB T2H 1S2

I need to put the data into 5 columns:

Name | Address |City|Province|Postal

We don't want FirstName LastName

I find I have to use a number of steps to do this ... any tips?

The final destination will be an Access table.



1 Answer Found

Answer #1    Answered By: Aadi Martin     Answered On: Aug 19

Are there many records? I'd seriously recommend getting the institution to
pull its finger out and give you another dump with tab or column  separation.
They should be ashamed of themselves.

My feeling is that the task of splitting the text  by spaces will be dwarfed
by the task of putting it back together again. The address, particularly,
will be a pain, as there will be variable numbers of words in it depending
on street name, city/county name, floor number, etc.

If there aren't too many records, you might be able to get a reasonable
approximation by a "both ends" approach:

- strip the first two words and use them as the name
- strip the last three words and use them as the city/province/postal
- use the rest as the address

Then go in and fix the 20% that won't fit this.

You might perhaps want to make it a two-step process. If you convert the
strings to comma-separated first and fix that, then you can easily break
them later at the commas into the component parts. It's much easier to move
a comma in a string  than to re-join bits that have been separated wrongly.

So, write a macro to put commas in the appropriate places and return another
Then export the stuff to a decent text editor or Word (Excel is not great
for this).
Then put the commas in the right places and save the file as a text file
with a csv extension.
Then open it back in Excel and Excel will do the comma-to-column split for
Finally, insert a row 1, put in headers, and re-save as a WorkBook.

Didn't find what you were looking for? Find more on String Handling in Excel Or get search suggestion and latest updates.