=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A2," A ",""),"
",""),"And",""),"The",""),5)&LEFT(SUBSTITUTE(SUBST ITUTE(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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"watto84 " wrote in message
...
im trying to join 2 fields together, but only taking the first 5
charcters of the first field and the first 3 characters of the 2nd
field. I can do this using the =replace(......) function, but i want to
be able to ignore 'spaces' in the fields aswell as common text such as
'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 this
"The CMel" as you can see it takes the first 5 and first 3 letters
respectively from each field. I want to ignore 'spaces' and common
text, the result should be 'CompaMel'.
Is there any way i can do this??

thanks
Michael.


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.594 / Virus Database: 377 - Release Date: 24/02/2004