Ignoring text and spaces ???

  • Thread starter Thread starter watto84
  • Start date Start date
W

watto84

im trying to join 2 fields together, but only taking the first
charcters of the first field and the first 3 characters of the 2n
field. I can do this using the =replace(......) function, but i want t
be able to ignore 'spaces' in the fields aswell as common text such a
'A' and 'And'.
heres an example
Field1 = The Company Field2= Melbourne
at the moment using the replace function i can get it to look like thi
"The CMel" as you can see it takes the first 5 and first 3 letter
respectively from each field. I want to ignore 'spaces' and commo
text, the result should be 'CompaMel'.
Is there any way i can do this??

thanks
Michael
 
=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," A ",""),"
",""),"And",""),"The",""),5)&LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2
," A ","")," ",""),"And",""),"The",""),3)

should capture most, but will not strip out "A " if the company names begins
with "A ", eg A Company.
 
Back
Top