RegEx
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi RegEx Pro,
I'm trying to build a RegEx to tease out the name of the city without the extraneous info at the end. For example, here's my table:
HESPERIA CA
HESPERIA, CA
HESPERIA. CA
HESPERIA,, CA,
BOULDER& NV
BOULDER&NV
What I want to capture is HESPERIA and BOULDER leaving out the characters. The way I can tell that it's the end of the city name is that we have char(s) and/or space(s) before CA or NV. I've been doing this the long way by using RegEx and Text To Columns with one character at a time. I'm trying to figure out a way to factor in other miscellaneous characters before CA or NV that I might not be aware of.
Could somebody help me figuring out an expression in RegEx that will encapsulates all the miscellaneous characters before CA or NV and leaves the city name untouched?
Thanks,
K
Solved! Go to Solution.
- Labels:
- Regex
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks @DataNath! That works, I can consolidate the two fields into one.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @DataNath,
I noticed a couple more issues when I added more cities. Some of the city name letters got chopped off while others only picked up the first word of the city name. Could you please help me figure out how to revise the RegEx to accommodate for those exceptions?
Thanks,
K
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@knnwndlm I think the best approach here really is a quick cleanse step beforehand, then we can just use the same RegEx with a slight amendment which gets the desired output. Just be sure to bump up the size of the RegEx output fields a little to allow for bigger strings so that they're not truncated:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you @DataNath/! Much appreciate it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @DataNath ,
How do I incorporate cases in which the entire state name is spelled out in the city field? Here are a few examples:
HESPERIA, CALIFORNIA
HESPERIA CALIFORNIA
BOULDER CITY, NEVADA
I suppose that I could do a separate RegEx as follows: \s*CALIFORNIA\s*|\s*NEVADA\s*. Do I need a grouping for alternate? Or can this be left as is.
Thanks,
Konn
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
How do I encapsulate all the cases in RegEx as shown in the Excel file below? I was hoping to put all the various punctuations in { } in the RegEx as a way to segue out the extraneous info. However, I have no idea how to do that. What I want to have at the end of the day is to correct the ORIG_NAME to the CORRECTED_NAME in the Excel spreadsheet.
Could you please help me understand how to do this? I'm learning RegEx and it's quite challenging especially for those complicated ones.
Thanks,
Konn
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello. I am new to using this particular tool. I am trying to parse out the phrases below so that they only read "Foreign" or "Domestic". Can someone help me build this expression? I am still learning what each component means.

- « Previous
-
- 1
- 2
- Next »