Returning only a portion of a number

[TAG423]

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?

 

[davers5]

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

 

[Aladin Akyurek]

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)