LinkBack URL
About LinkBacks
Hi
I have one column of cells with values such as 0001.hk or 020.hk. I would like to have another column that extracts only the text "hk". Appreciate your help.
regards
LL
Hi
I have one column of cells with values such as 0001.hk or 020.hk. I would like to have another column that extracts only the text "hk". Appreciate your help.
regards
LL
=mid(a1,sumproduct((isnumber(--mid(a1,row(1:100),1))*1))+2,len(a1))
Thanks. This worked for my two examples. However, when I put in B20.si, I would like to see "si" but instead I see ".si". What is want is to extract the two alphabets to the right of the "." all the time. There can be any number of characters to the left of ".".
Try this
=IFERROR(MID(A1,FIND(".",A1)+1,5),"")
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
=right(a1,2) if its always only 2 characters
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Glad it worked for you and thank you for your feedback.
for general result is there any characters before or after the numbers try this one:
array formula
=MID(A1,MIN(IFERROR(IF(--MID(A1,ROW(1:100),1),ROW(1:100)),"")),COUNT(--MID(A1,ROW(1:100),1)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks