Need help with a search and replace...

Jeramey Beck

I have data that looks like this:

1 Bergey,Ben 8 Great Strides 11:26.91 1

I need to do a large search and replace so that my data looks like this:

1|Bergey,Ben|8|Great Strides|11:26.91|1

This will allow me to then import my data into Excel and get everything in the proper columns for data manipulation.

Any help would be greatly appreciated.

Terry R

With only 1 example I had to work with, I can only hope this will work for you. It did for me, even when the 1st, 2nd and last numbers were longer than 1 character.

So
Find what: ^(\d+)\s(.+?)\s(\d+)\s(.+?)\s([.0-9:]+)\s(\d+)
Replace with: \1\|\2\|\3\|\4\|\5\|\6

So this is a regular expression, that means you need to operate in “regular expression” search mode in the replace window (Ctrl-H). Also have ‘wrap around’ ticked. You can run it as single mode replace until sure you are happy with the results. Then click on replace all button and it will complete the changes for you in the current file.

Let us know if you require any changes, possibly due to other records not exactly fitting the example well enough to be picked up by my regex.

Terry

Terry R

I have another regex, this one is possibly a better option as I noticed all your instances of putting the ‘|’ character had either a number before or after.

So
Find what: \h(?=\d)|(?<=\d)\h
Replace with: \|

So as I said it will only deal with spaces that have a number on either side of it. Therefore it leaves the one alone in (as your example says) “Great Strides”

Terry