Using the position of the first letter in a string to replace the next/previous character

Hi I want to do the following:

1. Find the position(n) of the first character in a string

2. Then, check if (n+1) or (n-1) is equal to 0 then change it to O.

If I have 01234567890H123.

I want to change 0 in front of H to O.

I tried using regex_match and Fidnstring() to find the position but I don't know how to change the next/previous chracter.

Thank you

Datasets

Regex

Preparation

Expression

Accepted answers

All comments

apathetichell

This is the most direct way - add a record id for multiple records. tokenize by row. use a multi-row formula. sort. summarize to concatenate.

h20.yxmd

ds12

Hi, thank you for the help!

How would I put in an condition to make sure it doesn't change a 0 where it is supposed to be 0.

For example, if the original id was:

0123456780HL123 vs 0123456789OH123

I think it will have to be based on the locationinrecord position, but I'm not sure how to go about it.

Maybe I'm not understanding the workflow correctly, but I don't think it is actually changing it to O because the output stays the same when I change the letter to M in the then statement.

ds12

Hm, What do you mean by first character? This is what I got with the updated formula. the third column is my ideal output.

1 01234567890H123 ----- M123456789MH123 ----- 0123456789OH123
2 0123456780HL123 ----- M12345678MHL123 ----- 0123456780HL123

Are we able to put a check to make exclude the 0 in the first and 10th letter?

Thanks so much

apathetichell

Do you want it repeated for the +1/-1 0 next to every letter or only the first one?

You can add a check by position. When I put your record two in my text input - the the "0H" became "OH" as I thought you wanted?

ds12

oh! only the first one please

Right, the only difference would be the first row is OH123 whereas the second row is HL123 with a 0 in the front as part of the ID.

We wouldn't want to change the 0 that is part of the ID whereas the 0H123 is just "OH123" read in incorrectly from pdf.

Does that make sense?

apathetichell

And you can confirm that it's not always an H - right? I'll work on it. someone else might take a look and a new set of eyes might see something I'm not here in terms of easy solutions.

ds12

Yes I can confirm that. Thank you so much

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703