Math with Strings

[TBotNik]

All,

I have a number column, but format must be:

EO-01 to EO-n

Where n is 2 or more digits.

Nothing I've tried has worked and must be able to "right corner drag" the first cell in the page and propagate the column.

Cheers!

TBNK

[Richard Buttrey]

Hi and welcome to the forum,

Would you upload a workbook with a few examples so that we may see your request in context?

[leelnich]

Hi TBNK- enter 1 in A1, 2 in A2. Apply custom Number Format \EO-00 to both cells (Type it in). Now, WITH BOTH CELLS SELECTED, drag down to fill the series.

Untitled.png

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[FDibbins]

This will work for numbers up to 99...
="EO-"&TEXT(RIGHT(R2,2)+1,"00")
Will work on 100 and more

[FDibbins]

OK this works for 3 digits...
="EO-"&TEXT(RIGHT(R2,IF(LEN(R2)=6,3,2))+1,"00")
(with your data starting in R2)

[TBotNik]

OK this works for 3 digits...
="EO-"&TEXT(RIGHT(R2,IF(LEN(R2)=6,3,2))+1,"00")
(with your data starting in R2)

FDibbins,

Worked great!

Thanks!

TBNK

[leelnich]

Format the CELL with a custom Number format to keep the numbers AS NUMBERS, as in post #2:
Custom Number Format \EO-00 displays "EO-04", but the cell value = 4.

If you need them AS TEXT, use this formula (in A2 and down) (similar to Ford's post#5):
=TEXT(MID(A1,4,8)+1,"\EO-00")

Both approaches work with any number of digits. In each case, the # of zeros determines the minimum # of digits shown.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[FDibbins]

Great, happy to help