how to find and replace the following....

[nachumama]

Hi all, i'm having trouble replacing a number in an excel sheet.
xcopy "C:\change-computer-description.vbs" "\\GSLAB-01\c$\DEPLOY\" /y
xcopy "C:\change-computer-description.vbs" "\\GSLAB-01\c$\DEPLOY\" /y
xcopy "C:\change-computer-description.vbs" "\\GSLAB-01\c$\DEPLOY\" /y

I just want to change gslab-01 to go from gslab-01 to gslab-60.. every row fits in once cell which i expanded. i don't know if i am making myself clear.... thanks everyone.

i started this thread and since i dont know much about excel, can some one explain how to fix the original post?
thanks

[Special-K]

Just use Ctrl-F to find/replace

[Ace_XL]

or try the REPLACE or SUBSTITUTE functions perhaps

[roki4]

I am trying to figure this out and am stuck. Say the text to be replaced is entered in A1 and is entered as "GSLAB-01", then use this function: =REPLACE(A1,8,2,2).

The bolded part of the function, the new_text field, what function do you enter into there to make it perform "+1". If I could figure that part out, then it is just a matter of copying the formula down.

[Ace_XL]

try in A1 and drag below
=REPLACE(A1,8,2,ROW())

[roki4]

Ok, got it but still not perfect.

For rows 1 to 9 enter: =REPLACE(A1,8,2,ROW()+1) and drag down.
For rows 10 and above enter: =REPLACE(A9,7,2,ROW()+1) and drag down.

Not sure why it is adding an extra digit if I am starting the check at the 8th character for rows with more than 2 digits (i.e starting at row 10 and above).

This will work assuming your text is entered as "GSLAB-xx"

[Ace_XL]

Alternatively the consistent formula ..as long the text is "GSLAB-xx"

=REPLACE(A1,7,2,RIGHT(0&ROW()+1,2))

[roki4]

What does "0&" do before the ROW() function? I understand the rest of it, find the 2 characters starting from the right of the string.

Cheers for the lesson! Repped.

[Ace_XL]

If the ROW() function returns a single digit number, the preceding "0&" comes into play; if it returns a 2 digit number the '0&' is irrelevant since the Right function picks the last two characters only!

thnx for the rep

[nachumama]

i started this thread and since i dont know much about excel, can some one explain how to fix the original post?
thanks

[roki4]

In your first post, there should be an edit button/link to revise your original post.

[Cutter]

Sorry nachumama, I missed the fact that your thread was hijacked. roki4 has received an infraction for that breach of the rules.

[roki4]

Assuming your text is entered in cell A1 in the same format as in your first post: =REPLACE(A1,53,2,RIGHT(0&ROW()+1,2))

...and just drag down. Happy to help provide a solution!

[Cutter]

Thank you for the negative rep, roki4.

[nachumama]

sorry to keep bothering you guys, roki4 where exactly do i enter that formula?

[Ace_XL]

I think in the column next to A1 i.e. B1

[roki4]

Thank you for the negative rep, roki4.

Anyway, moving forward, nachumama has this solved your issue? Cheers.