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

Max;242198 Wrote:
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

If the characters are always ".HK" try Ctrl +H - Find and Replace
If you need to eliminate the last three characters ( whatever they are
) try =left(a1,len(a1)-3) (supposing you data is in A1)


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67535

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

Best Regards,
FARAZ A. QURESHI


"Max" wrote:

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

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.

--
Rick (MVP - Excel)


"Max" wrote in message
...
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

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.

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Max" wrote in message
...
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