# Add 1 to a number contained in text.

1. ## Add 1 to a number contained in text.

Hi All,

I would be grateful if someone would kindly help me with a formula.

I am trying to add 1 to a number contained in text, and to return text.

In Cell - Original Text
A1 = A00102A
A2 = A00175A
A3 = A00641A

In Cell - Result
B1 = A00103A
B2 = A00176A
B3 = A00642A

Kindest regards

Diane

2. ## Re: Add 1 to a number contained in text.

will the first and last character always be a alpha character?
and numbers inbetween

3. ## Re: Add 1 to a number contained in text.

Hi etaf

Yes the first and last characters will always be a alpha character, and numbers in between.

At the moment there are 2 leading zeros in the examples given.

Thanks

4. ## Re: Add 1 to a number contained in text.

Hi,

Does this help?

Formula:
`Please Login or Register  to view this content.`

5. ## Re: Add 1 to a number contained in text.

In B1 copied down:

=LEFT(A1,1)&TEXT(MID(A1,2,5)+1,"00000")&RIGHT(A1,1)

As Etaf has suggested, your sample may not be properly representative of your real data, so this may not work for your entire dataset.

6. ## Re: Add 1 to a number contained in text.

Try this
Formula:
`Please Login or Register  to view this content.`

 v A B 1 A00102A A00103A 2 A00175A A00176A 3 A00641A A00642A

here is another way: if letters on the left and right are not always "A"
Formula:
`Please Login or Register  to view this content.`

7. ## Re: Add 1 to a number contained in text.

I am sorry for the late response.... I have been unable to log on to the site due to a firewall problem (I have been told!).

I did use sweep's suggestion in my spreadsheet, and also managed to test Alkey's formula, and both worked a treat. Sorry AliGW I didn't try yours, but every time I copied text the firewall blocked the site.

I just want to thank you all for sparing the time to help.... as always it is very much appreciated.

Kindest regards

Diane

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1