|
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! |
Hi
Into cell in row 1 enter the formula ="AMD" & TEXT(ROW(),"000") and copy down -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "gizmo" wrote in message ... 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! |
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 <atyahoo<dotcom ---- "gizmo" wrote in message ... 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! |
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" wrote in message ... 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 <atyahoo<dotcom ---- "gizmo" wrote in message ... 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! |
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 <atyahoo<dotcom ---- "gizmo" wrote in message ... 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... |
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 <atyahoo<dotcom ---- |
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<attarkon.ee Arvi Laanemets "gizmo" wrote in message ... 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" wrote in message ... 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 <atyahoo<dotcom ---- "gizmo" wrote in message ... 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! |
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" wrote in message ... 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<attarkon.ee Arvi Laanemets "gizmo" wrote in message ... 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" wrote in message ... 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 <atyahoo<dotcom ---- "gizmo" wrote in message ... 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! |
| All times are GMT +1. The time now is 04:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com