Replacing certain characters

[junkerjorg]

I have a .csv that I need to change certain characters in and was wondering if there is an easy way to accomplish this? The data in the column I need to change currently looks like this ::[ab1]N162:0/10 and I need to change it to ::[ab1]N162[0].10. Is there a fast way to automate this change?

[AliGW]

Well, based on the sole example given:

=SUBSTITUTE(A1,":0/10","[0].10")

But I’ll bet there’s more to it than that, so There are instructions at the top of the page explaining how to Create and attach a sample workbook.

[junkerjorg]

You are correct, there are several hundred rows in the column that all need to be corrected. Hopefully the sample has attached. It is column W that has the info I need to convert.

[Gregb11]

Yes, but what are THE RULES to do the conversion? Is it that:
1. The 3rd colon should be changed to a left bracket - [
2. Then the forward slash after that needs to be changed to a right bracket and decimal point - ].

[bebo021999]

What to do with these data?

[junkerjorg]

Yes, but what are THE RULES to do the conversion? Is it that:
1. The 3rd colon should be changed to a left bracket - [
2. Then the forward slash after that needs to be changed to a right bracket and decimal point - ].

Yes, that is exactly what needs to take place. Just the 3rd : and the / get replaced by [ and ]. respectively.

[AliGW]

In X6 copied down:

=SUBSTITUTE(SUBSTITUTE(W6,":","[",3),"/","]")

You need to address the query in post #5. This may deal with it:

=SUBSTITUTE(SUBSTITUTE(W6,":","[",3),"/","]")&IFERROR(FIND("/",W6),"]")

[junkerjorg]

Thank you. I can't see the image posted in post #5 for some reason. It just says there is an image attached but no actual image.

[junkerjorg]

What to do with these data?

For some reason I cannot see the attachment to determine what data you are referring to

[AliGW]

It refers to row 253 onwards, which I believe I've dealt with.

[junkerjorg]

Gotcha. Those simply remove the : and bracket the number after the colon. So instead of N100:163 it would become N100[163]

[AliGW]

Yes, that's what I guessed. Have you looked at my solutions and the attachment? Is it what you want?

If so, if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[junkerjorg]

Yes, it works perfectly for me. Thank you very much!

[AliGW]

No problem.