Solved: Formula for incrementing a number in the middle of text?

[Simon Lloyd]

I have created a formula to increment an ID number::

=IF(B3<>"",LEFT(A2,3) & MID(A2,4,1)+1&RIGHT(A2,3),"")
this formula is in A3, in Cell A2 i have typed the first ID number "AE 1 NW" and i wanted the ID number to increment for each entry, this formula works perfect but i was wondering if there is an easier way, I do not want to use VBA as my project will have plenty of that and i want to, where possible use Excels built in functions as they are much quicker and consume less memory.

Any ideas?

Regards,
SImon

[moa]

The way you are doing it seems easy enough. I'm sure there are a few ways to do it.

Like this:
=IF(B3<>"",REPLACE(A2,4,1,MID(A2,4,1)+1)

[moa]

Oh wait, what if your number goes to two digits? will this happen?

[mdmackillop]

How about a custom number format?
"AE "0" NW"

[Charlize]

For a number > 9=IF(B3<>"",LEFT(A2,3) & MID(A2,4,LEN(A2)-6)+1 & RIGHT(A2,3),"")Charlize

ps. if your codes are always three characters number three characters.

[Simon Lloyd]

Moa, yes digits will exceed 10 maybe even 100 or more!, Malcom the custom number format you have shown how will i increment that?
Charlize, thanks for the solution for numbers exceeding 9.

Regards,
Simon

[Charlize]

Try this one. You can even change the number of letters in your coding. So AEFG 1 XZSZSZ or A 1 ZZZ will still work.
=MID(A2,1,SEARCH(" ",A2,1)) & MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",MID(A2,SEARCH
(" ",A2,1)+1,LEN(A2))))+1 & RIGHT(A2,LEN(A2)-LEN(MID(A2,1,SEARCH(" ",A2,1)) & MID
(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",MID(A2,SEARCH(" ",A2,1)+1,LEN(A2))))+1))Charlize

ps. for easy reading I have added some returns. remove them if you want to use this formule.

[Bob Phillips]

This works even if the string is say AE 9 or 11 NW

=SUBSTITUTE(A2,MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),SUMPRODUCT(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},"")))),
--MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),SUMPRODUCT(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},""))))+1)

[moa]

=REPLACE(A2,4,SEARCH(" ",A2,4)-4,MID(A2,4,SEARCH(" ",A2,4)-4)+1)

If the number of characters don't change.

[mdmackillop]

Malcom the custom number format you have shown how will i increment that?
=A2+1
The problem with this approach is that the cell contains only a number. If you have multiple codes and need to do lookups etc., this simple solution won't work.

[Simon Lloyd]

Thanks all for the responses, here's some feedback!




=MID(A2,1,SEARCH(" ",A2,1)) & MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",MID(A2,SEARCH
(" ",A2,1)+1,LEN(A2))))+1 & RIGHT(A2,LEN(A2)-LEN(MID(A2,1,SEARCH(" ",A2,1)) & MID
(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",MID(A2,SEARCH(" ",A2,1)+1,LEN(A2))))+1))
This does the job, increments, copy down works ok, change a value and the rest increment from there.


=SUBSTITUTE(A2,MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),SU MPRODUCT(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},"")))),
--MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),SUMPRODUCT(LEN(A2 )-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},""))))+1)This works in as far as it always increments row to row, if i change a value it remains incremental from 1.


=REPLACE(A2,4,SEARCH(" ",A2,4)-4,MID(A2,4,SEARCH(" ",A2,4)-4)+1)
this works or seems to work just as Charlize's solution does!

Thank you all for some great responses!

Regards,
SImon

[mdmackillop]

You missed one!

[moa]

or two:




=If(B3<>"",LEFT(A2,3) & MID(A2,4,LEN(A2)-6)+1 & RIGHT(A2,3),"")


is pretty smart and simple. Didn't think about "LEN(A2)-6".

[Simon Lloyd]

You missed one!
Malcom my apologies, i didn't pursue your suggestion because of the restraints the solution had as there would eventually be multiple codes and the use of Vlookup.


or two:


http://vbaexpress.com/forum/images/quotes/quot-top-left.gifQuote:http://vbaexpress.com/forum/images/quotes/quot-top-right.gif http://vbaexpress.com/forum/images/quotes/quot-by-left.gifOriginally Posted by: Charlize http://vbaexpress.com/forum/images/quotes/quot-by-right.gifhttp://vbaexpress.com/forum/images/quotes/quot-top-right-10.gif
=If(B3<>"",LEFT(A2,3) & MID(A2,4,LEN(A2)-6)+1 & RIGHT(A2,3),"")

http://vbaexpress.com/forum/images/quotes/quot-bot-left.gifhttp://vbaexpress.com/forum/images/quotes/quot-bot-right.gif

is pretty smart and simple. Didn't think about "LEN(A2)-6".

Glen i also didnt look at this solution by Charlize because he supplied another more complex solution for which i provided feedback!

I really appreciated the clever and varied responses to something i thought had very few alternative solutions.

In the end i went with Charlize solution because of the flexiblity of changing the suffix and prefix of the code, if Susans business takes off this would be a must!

Very kind regards to you all!

Simon