• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Separate text from digits from a cell

Z

zeal7619

New Member
Hi,

It may be simple for many, but not me.

I have numbers and alphabets in a column. Each cell will have different set of numbers and alphabets. I would like to separate alphabets from numbers in a cell. If anyone help me with the formula. I know i can do it with "=Left (A2,8)" but i have incosistant numbers in a cell. sometimes only 3 digits and sometimes 8 digits. Please see the attached data. I would really appreciate if any one has any ideas.
 

Attachments

Do you want hyphens included in Number portion? If yes...

Since your Text string is always 3 char length at end of string in your example.
Below will extract numbers portion
=SUBSTITUTE(A2,RIGHT(A2,3),"")

And below for text portion.
=RIGHT(A2,3)

If you want just numbers only excluding hyphen
=SUBSTITUTE(SUBSTITUTE(A2,RIGHT(A2,3),""),"-","")
 
You must log in or register to reply here.
Back
Top