CODEWITHSUNDEEP
excelexcel-formulaworksheet
Waseem
Waseem

Reputation: 19

Need a worksheet formula to detect if a delimiter in a cell should be disregarded among all others

I need to parse a string in a cell where there are multiple delimiters and I need to figure if one of them should be disregarded or not.

Some of the Delimiters at some point in the string should be skipped and that portion of the string should not be parsed. For example if I have the string

123, Main, Street, Apt ABC, New York, NY, 12345,6789, USA.

This looks like an address. If I use the delimiter "," then the # and street name will be split from each other but they should be together, and the ZIP Code will be split into two sections, but they need to stay together and the "," should be changed to a "-" How do I write a worksheet function that would change that particular ',' into a dash and not the other delimiters?

Currently I am doing two operations for the ZIP code, parse and then concatenate, prefer to do one if achievable!

Similarly if I am using Text To Columns and I have this string:

123 Main Street Apt ABC New York NY 12345-6789 USA

If I use the 'Space' as a delimiter then New and York will be split. I can't use Fixed length because the strings are not all the same length.

I have many cases that look like these two examples. And I am stumped on how to fix the City portion of it. Short of having a list of two-word city names I am not sure how this can be done, if at all.

Thanks for any creative ideas. And if it can't be done, I will accept it and do it manually.

Upvotes: 0

Views: 109

Answers (1)

Ben
Ben

Reputation: 271

Unfortunately, programmatically with either a custom built excel function or a macro there will be no way to discern the difference between commas.

Uniquely, if you are able to count and say that only a specific one is needed, say the 4th comma is needed and 5th, 6th commas then the 7th should be a dash. That can be programmed into a macro that will accomplish it. But unless you can be unique about the number of delimiters only the X and Y numbered ones are needed it will be difficult to do it with any function or VBA code.

If you can use unique numbering then it is possible, otherwise it would need to be done manually.

Upvotes: 0

Related Questions