Formatting Text

[tycasey17]

I am trying to have a number that is consistent with a 1111-22-333-4444 format to have a shortened version of 22-333-4444. At times, it will have a letter in the string or it the "22" section will start with a 0. I have used the two formats going through the FORMAT CELLS > NUMBER > CUSTOM > 00-000-0000 or ##-###-#### and I have used the formulas of =TEXT(RIGHT(A1,9),"00-000-0000") and/or =TEXT(RIGHT(A1,9),"##-###-####"). I need the number to be properly displayed for use in other tabs.

Is there a way to have the number regardless if it starts with a 0 or has a letter of some sort?


Example:
Original Input: 1234-1C-234-5678
=TEXT(RIGHT(A1,9),"00-000-0000") displays 1C2345678

Original Input: 1234-01-234-5678
=TEXT(RIGHT(A1,9),"##-###-####") displays 1-234-5678

 

[gordsky]

try this

=RIGHT(TEXT(A1,0000-00-000-00000),11)

 

[Micron]

Maybe
=MID(A1,6,11)

Never mind. I thought you were trying to return only a portion of a string, but you're trying to apply a format. But I do get the desired results??

1111-22-333-4444	22-333-4444
1234-1C-234-5678	1C-234-5678

Weird that in Excel, MID requires a value for number of characters to be taken. Access does not require this, and if not provided, takes everything after the start point by default.