Increment Numeric Part Of Alphanumeric Text

    I have a cell with a value of, Text 1 and in the next field I want it to display Text 2, then text 3 and so on.


    Is there any way of doing this? I guess I am looking for something like a1+1, just a shame it doesn't work.


    Nick

    Re: Adding Numeric Values To Text


    ="Text " & COLUMN(A1)


    the copy to rght

    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

    Re: Adding Numeric Values To Text


    Another possibility...


    1) Enter Text 1 in a cell


    2) Move the cursor over to the lower right corner of the cell until the cursor changes


    3) Click and drag across


    Hope this helps!

    Re: Adding Numeric Values To Text


    OK, sorry perhaps I wasn't very clear.....


    This spreadsheet is to build test cases, the idea behind it being that the test cases are built against conditions so for example.


    Condition number (1.1)would be c2, Test step number (Step 1)f2, then if the same condition number is entered in c3, a formula IF(c2=c3,"f2+1,"Step 1") would be used. The Test Step number field is a validation list made from a named range, with steps 1 - 50.


    So the test steps belong to the same condition the steps increase by 1, if the condition number changes then it reverts back to step 1.


    Hope this makes it clearer.


    Example attached if not.

    Re: Adding Numeric Values To Text


    ="step " & COUNTIF(C$2:C2;C2)


    copy above into F2 and copy down as far as needed. Maybe you need to replace ; with ,

    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

    Re: Adding Numeric Values To Text


    Hi NickW,


    This addresses blank rows and adds 1 to previous step as requested.


    =IF(C3= "","",IF(C3=C2,"Step " & MID(F13,5,2)+1,"Step 1"))


    Paste in C3 and copy down.

    Re: Adding Numeric Values To Text


    Hi, I am having problems with this formula again.


    I am using the following formula in the attached expample.


    =IF(A2="",""," step "&COUNTIF(A$1:A2,A2))


    but when the cells in a contain 3.1 and 3.10 excel reads them as the same thing, so the count for 3.10 becomes step2 whereas it should be step 1


    Example attached as my explanations are rubbish! :smile:

    Re: Adding Numeric Values To Text


    In D1 and copy down,


    [COLOR="Blue"]=IF(A1 = "", "", "Step " & SUMPRODUCT( --(A$1:A1 = A1)) )[/COLOR]


    Nick, two people offered suggestions to you three weeks ago and never got a response until now. You're going to find Oz a lonely place if you keep doing that.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

    Re: Increment Numeric Part Of Alphanumeric Text


    Thanks for the reply.


    I realise I should have replied to the other posts and I am very sorry for not doing so, this is a great place for help and I will make sure not to abuse it.


    Once again thanks for solving this one for me.


    A very humble Nick W

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login