Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Returning only a portion of a number

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have large numbers in one column.
    2345900-4039klj
    2345900-7690lgn
    2345900-3670ghl
    etc.....

    In the next column, I only need the last four numbers with an apostrophe in front.
    '4039
    '7690
    '3670
    etc......

    What is the formula for this?


  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If the numbers are all in the same format as those examples you can use:
    ="'" & mid(cell with the number),9,4)

    this will take the number from that cell and take return 4 characters starting from the 9th one.

    If the number formats vary you can still do it but it will be a longer formula and likely nead to use the search() and len() functions.

    Dave

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-03-21 13:44, TAG423 wrote:
    I have large numbers in one column.
    2345900-4039klj
    2345900-7690lgn
    2345900-3670ghl
    etc.....

    In the next column, I only need the last four numbers with an apostrophe in front.
    '4039
    '7690
    '3670
    etc......

    What is the formula for this?

    If the data is regular as your sample shows, use:

    =MID(A1,SEARCH("-",A1)+1,4)

    The result is a text-formatted number.

    If you need the apostrophe, use:

    ="'"&MID(A1,SEARCH("-",A1)+1,4)


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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