Replacing specific text by “”

[graiggoriz]

Hello,

I have a reference in my workbook that I no longer need starting by F (+6 number) and I (+5 number) following by :

example: I12345: F123456:

In the cell A1 I have the following text:

A1 = "I12345: Formal Data - Reports or SQL data purchase"

C1 = "F178456: PROC Performance

F178457: PROC System testing

F178458: PROC testing”


My goal is to get

A1 = "Formal Data - Reports or SQL data purchase"

C1 = "PROC Performance

PROC System testing

PROC testing”

Would you know if a formula can do that? I have tried with =REPLACE(A1;1;8;"") but I could only remove the references at the beginning of the cell. Which is not what I want.

EG: C1 = "PROC Performance

F178457: PROC System testing

F178458: PROC testing"



You may have a better formula

Many thanks in advance,
Graig

[Pepe Le Mokko]

Try

Please Login or Register  to view this content.

[bebo021999]

With A1 probably is:

"I12345: Formal Data - Reports or SQL data purchase"
"F123456: Formal Data - Reports or SQL data purchase"
"Formal Data - Reports or SQL data purchase"
" 12345: Formal Data - Reports or SQL data purchase" (With : )
"F : XXXX" (With F and : )

Please Login or Register  to view this content.

[graiggoriz]

Hello,

It Works only on the first reference.

[JeteMc]

It might make it easier to help if we could see a sample .xlsx file (instructions given in banner at the top of the page).
Let us know if you have any questions.

[hrlngrv]

I've never understood the preference for RIGHT(s,LEN(s)-FIND(x,s)) vs the arguably clearer REPLACE(s,1,FIND(x,s),"") with one fewer function call.

Then again, if only Excel had regular expressions like in LibreOffice Calc. =REGEX(A7,"\b[FI]\d+: *","","g")

[hrlngrv]

This something Excel handles rather poorly. You could try using the Find and Replace dialog. Select the range needing these edits. Press [Ctrl]+H to display the dialog. First enter F??????: as Find what, clear the field for Replace with, and click on the Replace All button. Then enter I?????: as Find what and click on the Replace All button.

Clarification: The Find what entries above include a space character following the colon.

The only robust way to do this with formulas is to write a user-defined function in VBA which makes use of the Windows Script Host DLL's regular expression object. Are you open to using VBA?