+ Reply to Thread
Results 1 to 6 of 6

Just take the text and ignore the numbers

  1. #1
    Forum Contributor
    Join Date
    08-01-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    182

    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.

    Thanks in Advance..
    Last edited by inayat; 09-05-2011 at 04:01 AM.

  2. #2
    Registered User
    Join Date
    04-18-2011
    Location
    Surrey, UK
    MS-Off Ver
    Excel 2003
    Posts
    29

    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. #3
    Forum Contributor
    Join Date
    08-01-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    182

    Re: Just take the text and ignore the numbers

    Quote Originally Posted by PaulG2011 View Post
    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. #4
    Registered User
    Join Date
    04-18-2011
    Location
    Surrey, UK
    MS-Off Ver
    Excel 2003
    Posts
    29

    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. #5
    Forum Contributor
    Join Date
    08-01-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    182

    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. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Just take the text and ignore the numbers

    I can't see any VBA involved
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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