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

[ds12]

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

[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.

[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.

[apathetichell]

Modified... I had some errors in my multi-row it should read:

if [Row-1:locationinrecord] = null() then [field1] elseif regex_match([field1],"\d") and (!regex_match([Row+1:Field1],"\d") or !regex_match([Row-1:Field1],"\d+")) and ([Field1]="0") then "O" else [field1] endif

this tests to:

a) make sure it's not the first row. (otherwise row1 if it was 0 would be a "O")

b) that it's a digit equal to zero

c) that the adjacent character is a letter.

If so it changes it - otherwise it keeps it the same... One thing I'm not sure about - is it only the first character or all of them that could create the change? If it's the first one, I'll have to make some changes.

[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