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
will the first and last character always be a alpha character?
and numbers inbetween
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
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
Hi,
Does this help?
Formula:
Please Login or Register to view this content.
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
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.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
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.
Last edited by AlKey; 08-23-2017 at 11:52 AM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
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
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks