+ Reply to Thread
Results 1 to 8 of 8

Formula to extend a string of type AAA001

  1. #1
    gizmo
    Guest

    Formula to extend a string of type AAA001

    Hi guys,

    I'm wondering if there is any formula that can replace the manual dragging
    of the cell for values composed of letters and numbers. The concrete exemple
    would be:

    AMD001, AMD002, AMD003 etc...

    THanks a lot and a happy new year!



  2. #2
    Arvi Laanemets
    Guest

    Re: Formula to extend a string of type AAA001

    Hi

    Into cell in row 1 enter the formula
    ="AMD" & TEXT(ROW(),"000")
    and copy down


    --
    When sending mail, use address arvil<at>tarkon.ee
    Arvi Laanemets


    "gizmo" <[email protected]> wrote in message
    news:[email protected]...
    > Hi guys,
    >
    > I'm wondering if there is any formula that can replace the manual dragging
    > of the cell for values composed of letters and numbers. The concrete

    exemple
    > would be:
    >
    > AMD001, AMD002, AMD003 etc...
    >
    > THanks a lot and a happy new year!
    >
    >




  3. #3
    Max
    Guest

    Re: Formula to extend a string of type AAA001

    Not sure what you're after (Edit > Fill > Series > Columns .. ??),
    but you could play with this ..

    Select say, A1:A20

    Put in the formula bar:
    ="AMD"&TEXT(ROW(),"000")

    Array-enter, i.e. press CTRL+SHIFT+ENTER
    instead of just pressing ENTER

    The above will fill the range with: AMD001, ... AMD020
    (w/o dragging down <g>)

    (but think it's easier just to put in A1: AMD001, and drag down)

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "gizmo" <[email protected]> wrote in message
    news:[email protected]...
    > Hi guys,
    >
    > I'm wondering if there is any formula that can replace the manual dragging
    > of the cell for values composed of letters and numbers. The concrete

    exemple
    > would be:
    >
    > AMD001, AMD002, AMD003 etc...
    >
    > THanks a lot and a happy new year!
    >
    >




  4. #4
    gizmo
    Guest

    Re: Formula to extend a string of type AAA001

    Thanks guys for your input!
    However, maybe I was not explicit enough...

    The goal would be to let one cell unprotected (f. ex. B2), where somebody
    will put the adequate value (one day it can be AMD005, other day it can be
    AMD558), and then (in adjacent columns or rows) put in place an automatic
    formula that will extend this value once and for all, so nobody will have to
    drag it manually...

    With sole numbers, the task would be "kinderleicht" (B2 for input, C2 with
    formula =B2+1, then D2 with formula C2+1 etc...). However, once we have a
    mix of letters and numbers, the whole exercice becomes more complicated (at
    least for me:-)).

    If you could help me on this also...


    "Max" <[email protected]> wrote in message
    news:[email protected]...
    > Not sure what you're after (Edit > Fill > Series > Columns .. ??),
    > but you could play with this ..
    >
    > Select say, A1:A20
    >
    > Put in the formula bar:
    > ="AMD"&TEXT(ROW(),"000")
    >
    > Array-enter, i.e. press CTRL+SHIFT+ENTER
    > instead of just pressing ENTER
    >
    > The above will fill the range with: AMD001, ... AMD020
    > (w/o dragging down <g>)
    >
    > (but think it's easier just to put in A1: AMD001, and drag down)
    >
    > --
    > Rgds
    > Max
    > xl 97
    > ---
    > GMT+8, 1° 22' N 103° 45' E
    > xdemechanik <at>yahoo<dot>com
    > ----
    > "gizmo" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hi guys,
    > >
    > > I'm wondering if there is any formula that can replace the manual

    dragging
    > > of the cell for values composed of letters and numbers. The concrete

    > exemple
    > > would be:
    > >
    > > AMD001, AMD002, AMD003 etc...
    > >
    > > THanks a lot and a happy new year!
    > >
    > >

    >
    >




  5. #5
    Max
    Guest

    Re: Formula to extend a string of type AAA001

    Let's say we earmark:

    B1: AMD (for input of letters)
    B2: 5 (for input of first number)

    Put in B3: =$B$1&TEXT($B$2+ROWS($A$1:A1)-1,"000")
    Copy B3 down by as many rows as desired, say to B100

    B3:B100 will then return the sequential alphanumerics:
    AMD005, ... AMD102
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "gizmo" <[email protected]> wrote in message
    news:#[email protected]...
    > Thanks guys for your input!
    > However, maybe I was not explicit enough...
    >
    > The goal would be to let one cell unprotected (f. ex. B2), where somebody
    > will put the adequate value (one day it can be AMD005, other day it can be
    > AMD558), and then (in adjacent columns or rows) put in place an automatic
    > formula that will extend this value once and for all, so nobody will have

    to
    > drag it manually...
    >
    > With sole numbers, the task would be "kinderleicht" (B2 for input, C2 with
    > formula =B2+1, then D2 with formula C2+1 etc...). However, once we have a
    > mix of letters and numbers, the whole exercice becomes more complicated

    (at
    > least for me:-)).
    >
    > If you could help me on this also...




  6. #6
    Max
    Guest

    Re: Formula to extend a string of type AAA001

    And to fill across from B3,
    just change ROWS to COLUMNS in the formula:

    > Put in B3: =$B$1&TEXT($B$2+ROWS($A$1:A1)-1,"000")


    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----



  7. #7
    Arvi Laanemets
    Guest

    Re: Formula to extend a string of type AAA001

    Hi

    Are last 3 characters always numbers? When yes, then with start value
    entered into B2
    B3=LEFT($B2,LEN($B2)-3) & TEXT(VALUE(RIGHT($B2,3))+1,"000")
    and copy down


    --
    When sending mail, use address arvil<at>tarkon.ee
    Arvi Laanemets


    "gizmo" <[email protected]> wrote in message
    news:%[email protected]...
    > Thanks guys for your input!
    > However, maybe I was not explicit enough...
    >
    > The goal would be to let one cell unprotected (f. ex. B2), where somebody
    > will put the adequate value (one day it can be AMD005, other day it can be
    > AMD558), and then (in adjacent columns or rows) put in place an automatic
    > formula that will extend this value once and for all, so nobody will have

    to
    > drag it manually...
    >
    > With sole numbers, the task would be "kinderleicht" (B2 for input, C2 with
    > formula =B2+1, then D2 with formula C2+1 etc...). However, once we have a
    > mix of letters and numbers, the whole exercice becomes more complicated

    (at
    > least for me:-)).
    >
    > If you could help me on this also...
    >
    >
    > "Max" <[email protected]> wrote in message
    > news:[email protected]...
    > > Not sure what you're after (Edit > Fill > Series > Columns .. ??),
    > > but you could play with this ..
    > >
    > > Select say, A1:A20
    > >
    > > Put in the formula bar:
    > > ="AMD"&TEXT(ROW(),"000")
    > >
    > > Array-enter, i.e. press CTRL+SHIFT+ENTER
    > > instead of just pressing ENTER
    > >
    > > The above will fill the range with: AMD001, ... AMD020
    > > (w/o dragging down <g>)
    > >
    > > (but think it's easier just to put in A1: AMD001, and drag down)
    > >
    > > --
    > > Rgds
    > > Max
    > > xl 97
    > > ---
    > > GMT+8, 1° 22' N 103° 45' E
    > > xdemechanik <at>yahoo<dot>com
    > > ----
    > > "gizmo" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Hi guys,
    > > >
    > > > I'm wondering if there is any formula that can replace the manual

    > dragging
    > > > of the cell for values composed of letters and numbers. The concrete

    > > exemple
    > > > would be:
    > > >
    > > > AMD001, AMD002, AMD003 etc...
    > > >
    > > > THanks a lot and a happy new year!
    > > >
    > > >

    > >
    > >

    >
    >




  8. #8
    gizmo
    Guest

    Re: Formula to extend a string of type AAA001

    Thanks once again guys, and especially for your last suggestion Arvi!

    It looks quite sophisticated (compared to simple dragging down), but works
    perfectly!!
    My best wishes,

    Gizmo


    "Arvi Laanemets" <[email protected]> wrote in message
    news:[email protected]...
    > Hi
    >
    > Are last 3 characters always numbers? When yes, then with start value
    > entered into B2
    > B3=LEFT($B2,LEN($B2)-3) & TEXT(VALUE(RIGHT($B2,3))+1,"000")
    > and copy down
    >
    >
    > --
    > When sending mail, use address arvil<at>tarkon.ee
    > Arvi Laanemets
    >
    >
    > "gizmo" <[email protected]> wrote in message
    > news:%[email protected]...
    > > Thanks guys for your input!
    > > However, maybe I was not explicit enough...
    > >
    > > The goal would be to let one cell unprotected (f. ex. B2), where

    somebody
    > > will put the adequate value (one day it can be AMD005, other day it can

    be
    > > AMD558), and then (in adjacent columns or rows) put in place an

    automatic
    > > formula that will extend this value once and for all, so nobody will

    have
    > to
    > > drag it manually...
    > >
    > > With sole numbers, the task would be "kinderleicht" (B2 for input, C2

    with
    > > formula =B2+1, then D2 with formula C2+1 etc...). However, once we have

    a
    > > mix of letters and numbers, the whole exercice becomes more complicated

    > (at
    > > least for me:-)).
    > >
    > > If you could help me on this also...
    > >
    > >
    > > "Max" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Not sure what you're after (Edit > Fill > Series > Columns .. ??),
    > > > but you could play with this ..
    > > >
    > > > Select say, A1:A20
    > > >
    > > > Put in the formula bar:
    > > > ="AMD"&TEXT(ROW(),"000")
    > > >
    > > > Array-enter, i.e. press CTRL+SHIFT+ENTER
    > > > instead of just pressing ENTER
    > > >
    > > > The above will fill the range with: AMD001, ... AMD020
    > > > (w/o dragging down <g>)
    > > >
    > > > (but think it's easier just to put in A1: AMD001, and drag down)
    > > >
    > > > --
    > > > Rgds
    > > > Max
    > > > xl 97
    > > > ---
    > > > GMT+8, 1° 22' N 103° 45' E
    > > > xdemechanik <at>yahoo<dot>com
    > > > ----
    > > > "gizmo" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > Hi guys,
    > > > >
    > > > > I'm wondering if there is any formula that can replace the manual

    > > dragging
    > > > > of the cell for values composed of letters and numbers. The concrete
    > > > exemple
    > > > > would be:
    > > > >
    > > > > AMD001, AMD002, AMD003 etc...
    > > > >
    > > > > THanks a lot and a happy new year!
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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