Cutting Part of Text

[Max]

Hi Guys, I have rows of data with varying text and numerical data. I want to
cut out some of the text but the problem I have is that I don't think I can
use =LEFT because the text from the left hand side is variable in length,
e.g. 735.HK or 1862.HK. What I would like to do is simply remove the .HK
part. Is there a function I can use to cut the predictable 3 characters at
the end (.HK) and and return the unpredictable 3 or 4 characters at the
beginning please?
Thanks

 

[Rick Rothstein]

Is there always a "dot" immediately following the number? If so, try this
either this...

=--LEFT(A2,FIND(".",A2)-1)

or, if you might be copying the formula down through cells that do not
contain entries, this...

=IF(A2="","",--LEFT(A2,FIND(".",A2)-1))

If a "dot" does not always follow the number, then try this...

=LOOKUP(9.9E+307,--LEFT(A2,ROW($1:$99)))

or this...

=IF(A2="","",LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99))))

again, depending on if it will be use on cells that either have or do not
have entries in them.

 

[FARAZ QURESHI]

Assuming your data to be in A1,
USE:
=LEFT(A1,LEN(A1)-3)
OR
=SUBSTITUTE(A1,".HK","")

 

[Max]

Thanks to all you guys, this has been great. The LEN function worked a treat.
Has all been noted for future use though. Thanks again guys.