Any way to Fill that's complex?

[BucSox79]

I am doing some work that involves adding similar groups to a sheet. To make it easier to explain here's this:

18048 Feeder 1 Phase 2 Over Voltage
18048 Feeder 1 Phase 2 High Current
18048 Feeder 1 Phase 3 Loss of Voltage
18048 Feeder 1 Phase 3 Over Voltage
18048 Feeder 1 Phase 3 High Current
18048 Low External Battery Voltage
18048 External Battery Test
18048 Feeder 1 VARs Change
18048 Undefined
18048 Undefined
18048 Undefined
18048 Undefined
18048 Undefined
18048 Undefined
18048 Undefined
18048 Undefined
18048 Undefined
18048 Undefined
*18048 DEVICE ENABLED
*18048 SCHEDULED POLLS ENABLED
*18048 UNSOLICITED RESPONSES ENABLED
*18048 DEMAND POLL IN PROGRESS
*18048 DEVICE RESTART IN PROGRESS
*18048 TIMESYNC IN PROGRESS
*18048 PROPAGATION DELAY IN PROGRESS
*18048 PRIMARY COM CHANNEL OK
*18048 SECONDARY COM CHANNEL OK
*18048 PRIMARY COM CHANNEL IN USE
*18048 SECONDARY COM CHANNEL IN USE
*18048 IIN RESTART BIT STATE
*18048 IIN TROUBLE BIT STATE
*18048 IIN LOCAL BIT STATE
*18048 IIN BUFFER OVERFLOW BIT STATE
*18048 IIN CONFIG. CORRUPT BIT STATE


Now the next group will have 18049 all other text the same, then 18050 and so on. Excel is not smart enough to see the pattern so i cant just drag down and have it fill for me. Is there any possible way to do this so it's easier and I dont have to 'copy, past, replace' 'copy, past, replace' 'copy, past, replace' over and over?

[JBeaucaire]

Is that all in ONE cell per line, or are those indicative of pieces of info in separate cells? If you can put the numbers in one column and the text in another, then you could use a formatting trick to do this.

If A2 has 18048 in it and all the rest of the text were in B2, then that sample set would go down to row 35. In A36, enter this formula:

=IF(LEN(A2)=5,RIGHT(A2,5)+1,"*"&RIGHT(A2,5)+1)

And in B36, just enter:

=B2

Now copy those two cells down as far as you want, 1000s of cells until you have all the sets you want. Highlight the entire set of data, Ctrl-C to copy, then Edit > Paste Special > Values to remove the formulas and lock the values in as real entries.

[BucSox79]

Unfortunately they are all in one column. Im thinking perhaps I can un-concatenate somehow then try what you suggested

[BucSox79]

ALright, that did it. Thank you so much.

[BucSox79]

Is that all in ONE cell per line, or are those indicative of pieces of info in separate cells? If you can put the numbers in one column and the text in another, then you could use a formatting trick to do this.

If A2 has 18048 in it and all the rest of the text were in B2, then that sample set would go down to row 35. In A36, enter this formula:

=IF(LEN(A2)=5,RIGHT(A2,5)+1,"*"&RIGHT(A2,5)+1)

And in B36, just enter:

=B2

Now copy those two cells down as far as you want, 1000s of cells until you have all the sets you want. Highlight the entire set of data, Ctrl-C to copy, then Edit > Paste Special > Values to remove the formulas and lock the values in as real entries.

On last thing, in your example, how did you separate the first numbers/* w/ numbers from the rest of the text and keeping the rest of the text in one cell?

[martindwilson]

if its re written slightly (as theres no reason for RIGHT(A2,5)+1 !)
=IF(LEN(A2)=5,A2+1,"*"&RIGHT(A2,5)+1)
it says
if a2 is 5 characters long add 1 to it
if its not 5 characters long then take the 5 rightmost characters an add 1 to them and while you,re at it add a star in front. The "*"& does that.
it could actually be written
=IF(LEFT(A2)="*","*"&MID(A2,2,255)+1,A2+1)
which would work with any number from 1 and *1 up

[JBeaucaire]

On last thing, in your example, how did you separate the first numbers/* w/ numbers from the rest of the text and keeping the rest of the text in one cell?

I used the Data > Text to Columns function. I used Fixed Width and manually selected only the first set of data to "split off", I left the rest together.

[JBeaucaire]

...as theres no reason for RIGHT(A2,5)+1 !
=IF(LEN(A2)=5,A2+1,"*"&RIGHT(A2,5)+1)

Yep. no reason other than me being too lazy to change each half of the IF/THEN.

[BucSox79]

if its re written slightly (as theres no reason for RIGHT(A2,5)+1 !)
=IF(LEN(A2)=5,A2+1,"*"&RIGHT(A2,5)+1)
it says
if a2 is 5 characters long add 1 to it
if its not 5 characters long then take the 5 rightmost characters an add 1 to them and while you,re at it add a star in front. The "*"& does that.
it could actually be written
=IF(LEFT(A2)="*","*"&MID(A2,2,255)+1,A2+1)
which would work with any number from 1 and *1 up

What is the opposite of &? If & adds something to the beginning, what would add something to the end? Turns out that come groups ive found have the stars at the end of the number. So much for people being consistent lol.

[BucSox79]

Disregard, i figured it out.

This can be marked as solved now. Thanks again guys

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]