Regex to add delimiter for parsing data

Hello! I'm currently using the following regex (\s{2}\b) to add a delimiter between my string so I can parse data into columns. I basically need to convert a text file into an excel file that can be used to perform analysis.

An example row of data is as follows:

5540134 9/22/22 .9649 432645 53.27 51.16 .11 10/28/22 221025401

Using this regex: \s{2}\b

Result:

5540194 |9/22/22 .9649 |432645 |53.27 |51.16 .11 |10/28/22 |221028001

This works fine for the most part except it does not add a delimiter (|) before the .9649 and .11 in the above example. There are many more rows like this, where a delimiter before a decimal point (.) is not added. These decimals are all missing the 0 prior to the decimal point (.) so how can I modify my regex here? Thank you!!!

Regex

Parse

Transformation

Accepted answers

Qiu

@jaiiracha
I was not able to do the same with (\s{2}\b).
and I am using below and hope that will work for you.

0207-jaisingh1.yxmd

All comments

Qiu

@jaiiracha
I was not able to do the same with (\s{2}\b).
and I am using below and hope that will work for you.

0207-jaisingh1.yxmd

jaiiracha

@Qiu Thank you for your reply. While this works for the given example, the rest of the rows are not in the same pattern so this would not work.

For e.g. the next row of data could be:

50927334 9/12/22 .96349 122645 334.27 51.16 .11 9/22/22 22101240133

so basically, what I am trying to do is add a delimiter based on the number of spaces (2 spaces) between each segment of the string. With the regex = \s{2}\b

I am able to add a delimiter between the string for all segments of the data except for the segments that begin with a decimal point (.9649, .11). Is there any way I could fix this?

This is the output that I currently get:

50927334| 9/12/22 .96349| 122645| 334.27| 51.16 .11| 9/22/22| 22101240133

This is the output that is needed:

50927334| 9/12/22| .96349| 122645| 334.27| 51.16| .11| 9/22/22| 22101240133

Notice in the desired output, there is a delimiter before .96349 and .11

Could you/anyone help with this?

Thank you!

Raj

This will work for you

binu_acs

@jaiiracha one way of doing this

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703