text concatinate with number

[j9070749]

In Y2 I have the number 342

in Y3 I have the text 'word'
I wish Y3 to have word342
Y4 to have word343
Y5 to have word344
Y6 to have word345

and so on ...

Thanks

[benishiryo]

hi j9070749, so is Y3 having just the text "word" or replaced by "word342"? you can try this in Y3

="word"&341+ROW(A1)

[j9070749]

Hi, thanks for the reply, this works for the first one, but doesn't increment when dragging the box down.

[Pepe Le Mokko]

Supposing you have 342 in Y1 and "word" in Y2 in Y3 enter

Please Login or Register  to view this content.

Pull down as needed

[Kevin UK]

Hi j9070749

benishiryo formula should copy down. you have'nt locked A1!

[j9070749]

Thankyou, this is working great.
The formula is now
=LEFT($A$2,5)&($Y$2+ROW()-3)

If possible, I want to moddify the forumla further.
If B:B changes (from A to B) I wish to add 20 the the number on the end (what I am concatinating A2 with).

I will try to give an example to make it more clear.

B Y
A text1
A text2
A text3
B text23
B text24

[Kevin UK]

How come cell A2 is now mentioned! And the LEFT function has appeared, I thought the text was "word"

[j9070749]

Hi, I got this sorted. The formula I used was:

=IF(A4="","",IF(D3<>D4,LEFT($A$2,5)&(RIGHT(Z3,4)+20),LEFT($A$2,5)&($Z$2+ROW()-3)))

[Kevin UK]

Absolutely nothing to do with your first post then!

[j9070749]

I just needed the concept, then I could adapt for my spreadsheet.

[twiggywales]

glad you solved this remember to mark solved. (although im not sure how much use the end result would be without some kind of reference sheet lol)

btw the formula seems over complex i may be incorrect but its possible that there could be a different more efficient way to deal with your problem (if you uploaded a book maybe someone could help)