# Just take the text and ignore the numbers

1. ## Just take the text and ignore the numbers

Hi All,

Just a small simple problem i have...
6297 : ABC

I just want a formula where i can just get ABC. Since ABC always will not be of specific number of characters, i can not use "Right" formula. Is there any formula in which we can just get the text and ignore the numbers and vice versa.

2. ## Re: Just take the text and ignore the numbers

Hi inayat,

Why does the string not being a set number of characters prevent you from using the RIGHT function?

If the numbers and text are always separated by a colon, you could use:

=RIGHT(A1, LEN(A1)-FIND(":",A1))

To get the characters to the right of the colon. You could modify this to:

=RIGHT(A1, LEN(A1)-FIND(":",A1)-1)

If there is always a space after the colon (and before the letters).

Similarly you can use the LEFT function get the numbers before the colon:

=LEFT(A1,FIND(":",A1)-2)

Are you saying that there might not always be a colon to separate your values?

Kind regards,

Paul

3. ## Re: Just take the text and ignore the numbers

Originally Posted by PaulG2011
Hi inayat,

Are you saying that there might not always be a colon to separate your values?
Hi Paul,

Thanks for your response. Yes there will always not be a colon to separate the values. Having said that i did not know the formula that you have sent, so thank you for that... Can you now help me without the colon.

Regards,

Inayat

4. ## Re: Just take the text and ignore the numbers

Hi inayat,

If there is no colon, what is separating your numbers and text - will there still be spaces like:

1234 ABC or will it be strings like:

1243CAB

If its the former, you can modify my formula to look for spaces instead of colons.

If your cell values will look like the bottom one - with no spaces or anything to separate them, I think you will need to write a short visual basic macro to do it, but some of the experts on here might be able to come up with an ingenius formula for you!

Kind regards,

Paul

5. ## Re: Just take the text and ignore the numbers

Thanks Paul...

For this problem the space will work as there always will be a space to separate the text...

Unfortunately I dont know visual basic macros... But for now my problem is resolved. Thanks again.. Sorry could not add to your reputation as I had already done that in the previous post

6. ## Re: Just take the text and ignore the numbers

I can't see any VBA involved

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1