replace multiple strings in a string

[FlorianH]

Hi there 🙂

unfortunately, I could not yet find a satisfying solution for the following problem:

I used the “Find Replace” function to match table A and table B. The order number in table B has always the same format (S-123456). Unfortunately, in table A the order number sometimes has another format (space before and/or after the hyphen or “s” instead of “S”, 5 “wrong” variants).

So before running the “Find Replace” function I want to bring the order numbers in table A into the correct format.

What would be the most elegant way to do that?

Thanks so much in advance!

[JulioMO]

Hi @FlorianH 

Here goes a workflow that performs what you want.

Regards

[messi007 · Accepted solution]

@FlorianH,

Please see below:

Attached the workflow, 

Regards,

[FlorianH]

Thanks so much for the Suggestion! At the end, I only used the first formula, but that helped me a lot!

[FlorianH]

Hey guys,

I guess you know that: once you found a solution, new requests and problems emerge. So I need your help again.

I built the following workflow, that fits my needs very well and that is pretty simple I guess.

But there are two problems.

row 2: why is "s- " not replaced by "S-"

and much more important!

row 5: it can happen, that there are 2 S-numbers in one cell. If that happens, I want to highlight that or I even better, I want to have both S-numbers in the RegExOut1 column.

Do you have any idea how to solve that?

Thanks so much in advance!

[messi007 · Accepted solution]

@FlorianH,

Please see below:

it's ugly with two regex but it do the job,

Hope this helps!

Regards,

[JulioMO · Accepted solution]

Hi @FlorianH 

The reason why the "s- " is not replaced by the "S-" is the following. 

In your formula tool, your third step will replace the "s-" by a "S-". Therefore, when the formula tool arrives to your fifth step, it won't find any "s- " to replace.

In order to fix it, put your actual third step as step number one. For the record, you can put the fourth as number two so you avoid more problems like this and eliminate your fifth step since there is no more needed.

Regards

[FlorianH]

Guys, you are awesome! Thank you so much for these solutions!