Changing part of text in 22000 lines!?

[Imandra]

Hi!
(Just want to add before reading that english is not my first language, trying to describe this problem the best I can! )
Edit: I'm using Excel 2007.
I'm working with a spare parts project, trying to connect spare parts to different individuals (machines). The spare parts are the same for all machines, but I want to connect each spare part multiple times to several specific machine id's.
My problem is that I've got 22000 spare parts.
In cell A1 through A150 I want the text to be FQL1612-01 all the way to FQL1612-150, and from cell A151 through A300 I want the text to read FQL1611-01 all the way to FQL1611-150 etc. continously all the way to cell 22000. 612, 611, 610 etc. being the machine id.
The text can not be separated.
Is there any way to just change the machine number (612, 611, 610, 609 etc) without changing the rest of the information?
I hope someone can help me, changing the machine id. manually will take me weeks!!!
Thankyou! (please ask me to explain more if needed!)

 

[Gerald Higgins]

Welcome to the board.

Here's what I would do (there may be better ways).

In B1, put
FQL1
Copy this all the way down to row 151.

In C1, put
612
Copy this all the way down to C150.
Then in C151, put
=C1-1

In D1 put
-
Copy this all the way down to D151.

In E1 put
1

In E2 put
=if(E1=150,1,E1+1)
Copy this all the way down to E151.

In A1, put
=B1&C1&D1&TEXT(E1,"00")
Copy this all the way down to A151.

Then, select A151:E151, and copy this down as far as required.

At the end, if you want, use Copy, Paste Special, to convert the results of the formulas in col A to values, and you can then delete the contents of cols B, C, D and E.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG02May09
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Spn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
 Spn = 12
    [COLOR="Navy"]For[/COLOR] n = 1 To 22000
       c = c + 1
        [COLOR="Navy"]If[/COLOR] c = 151 [COLOR="Navy"]Then[/COLOR]
            c = 1
            Spn = Spn - 1
        [COLOR="Navy"]End[/COLOR] If
            Txt = "FQL16" & Spn & "-"
            Num = IIf(c < 10, "0" & c, c)
            Range("A" & n) = Txt & Num
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MickG]

The previous code was incorrect , Try this:-

[COLOR="Navy"]Sub[/COLOR] MG02May02
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Spn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
 Spn = 612
    [COLOR="Navy"]For[/COLOR] n = 1 To 22000
       c = c + 1
        [COLOR="Navy"]If[/COLOR] c = 151 [COLOR="Navy"]Then[/COLOR]
            c = 1
            Spn = Spn - 1
        [COLOR="Navy"]End[/COLOR] If
            Txt = "FQL1" & Spn & "-"
            Num = IIf(c < 10, "0" & c, c)
            Range("A" & n) = Txt & Num
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Imandra]

Hi!
Thanks alot for the help, this will definitely make my job easier.
Great to find an active messageboard!

Best regards,
Imandra