+ Reply to Thread
Results 1 to 7 of 7

Add 1 to a number contained in text.

  1. #1
    Registered User
    Join Date
    04-19-2010
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    84

    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. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,514

    Re: Add 1 to a number contained in text.

    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.

  3. #3
    Registered User
    Join Date
    04-19-2010
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    84

    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. #4
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016
    Posts
    3,324

    Re: Add 1 to a number contained in text.

    Hi,

    Does this help?

    Formula: copy to clipboard
    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".

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,167

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Add 1 to a number contained in text.

    Try this
    Formula: copy to clipboard
    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: copy to clipboard
    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

  7. #7
    Registered User
    Join Date
    04-19-2010
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    84

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to round a number off when contained in text
    By hasdrubal in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-05-2017, 12:02 AM
  2. [SOLVED] Looking for a formula that indicates text not contained in a list.
    By Travisty in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 10-20-2015, 09:58 AM
  3. [SOLVED] If cell contains text contained in a named range return the row number
    By spoursy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-05-2014, 05:54 AM
  4. Sum if a number is contained in a list
    By pauldaddyadams in forum Excel General
    Replies: 8
    Last Post: 12-03-2013, 02:30 PM
  5. [SOLVED] Return specific text if a different text is contained anywhere in a range of cells
    By ciayers in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2013, 05:54 PM
  6. [SOLVED] Identify if a number is contained within 40 or more number ranges
    By HealthyB in forum Excel General
    Replies: 5
    Last Post: 02-10-2013, 08:22 PM
  7. Replies: 6
    Last Post: 03-07-2008, 03:40 PM

Bookmarks

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