Search and replace formula?

F

fastfrank

Hi,

I have a selection of data in this typical format:

f$\Data\Sharedf$\data1\Campaigns\BTCAT
e$\Data\Sharedf$\data5\Campaigns\BT EMail
j$\Data\Sharedj$\Business
h$\Data\Citrixf$\c1111111
f$\Data\Citrixf$\C222222

I want to be able to replace all instances where a letter is followe
by a '$' sign to a constant different letter for example 'e$'.

*f$*\Data\Shared*f$*\data1\Campaigns\BTCAT

Is there a formula I can use that can search and replace to give me th
correct output?

Thanks
Fran
 
B

Beege

fastfrank,

Have you tried edit/replace

enter ?$ in search
enter e$ in replace

Beege
 
R

Ron Coderre

FastFrank

Actually, the Edit/Replace should work fine using:
Find what: ?$
Replace with: e$
View the Options and make sure you UNCHECK: Match entire cel
contents


For an "inelegant" formula solution, try this......
With text in A1

B1
=SUBSTITUTE(SUBSTITUTE(A1,MID(A1,SEARCH("$\",A1)-1,2),"e$"),MID(SUBSTITUTE(A1,MID(A1,SEARCH("$\",A1)-1,2),"e$"),SEARCH("$\",MID(SUBSTITUTE(A1,MID(A1,SEARCH("$\",A1)-1,2),"e$"),3,255))+1,2),"e$")

Note: in case that formula gets altered in posting, there are NO space
in the formula.

Does that help?

Regards,
Ro
 
Top