If function help

[Frank]

Hi
excel 2003. I have a worksheet with a col (Col B) of GL#'s,
ex: 02-05-4568, 25-46-2004, etc. I want to change ONLY the last 4 digits to
the same #, say 6001. Also, the col next to this one (Col A) has a single
digit in it,. I need to change only the one;s that have a 2 in them?
So col A is single digit1-9. Col B is the GL# like above. All the GL#'s with
a 2 in the preceding row(which is col A) need to be changed.
Boy, I don't think I explained that too well.
I tried. Thanks so much
Frank

 

[JE McGimpsey]

Perhaps:

C1: =IF(A1=2, LEFT(B1,6) & "6001", B1)

 

[Rick Rothstein \(MVP - VB\)]

Perhaps:

C1: =IF(A1=2, LEFT(B1,6) & "6001", B1)

Another possibility is...

C1: =IF(A1=2,REPLACE(B1,7,4,"6001"))

Rick

 

[Frank]

Sorry, I know I didn't explain correctly.Colums are full from A-G. I don't
understand where to put the function . I will have to read a some more. I
also do not understand what the numbers 6, and 7&4 have to do with this? Ok
I need to read a lot, lot more!
Thanks
Frank

 

[David Biddulph]

If you are struggling to understand the syntax of an Excel function, type
its name into Excel's help facility. Every function [except DATEDIF()] has
an explanation, and usually examples, and pointers to related functions.

 

[JE McGimpsey]

Every function [except DATEDIF()] has an explanation, and usually
examples, and pointers to related functions.

And MacXL and WinXL00 include the DATEDIF() explanation.

 

[Frank]

I appreciate all the comments. I have much to learn. But in this case, I am
having a hard time understanding what the 7&4 are about in this function?
=IF(A1=2,REPLACE(B1,7,4,"6001"))
and what the 6 is about in this one? =IF(A1=2,REPLACE(B1,7,4,"6001"))
Thanks
Frank

If you are struggling to understand the syntax of an Excel function, type
its name into Excel's help facility. Every function [except DATEDIF()]
has an explanation, and usually examples, and pointers to related
functions.
--
David Biddulph

Sorry, I know I didn't explain correctly.Colums are full from A-G. I
don't understand where to put the function . I will have to read a some
more. I also do not understand what the numbers 6, and 7&4 have to do
with this? Ok I need to read a lot, lot more!
Thanks
Frank

 

[Gord Dibben]

Did you look at help on the REPLACE function?

If you did, you would find this explanation of the numbers.

REPLACE(old_text,start_num,num_chars,new_text)

Old_text is text in which you want to replace some characters.

Start_num is the position of the character in old_text that you want to
replace with new_text.

Num_chars is the number of characters in old_text that you want REPLACE to
replace with new_text.

New_text is the text that will replace characters in old_text.


Gord Dibben MS Excel MVP

I appreciate all the comments. I have much to learn. But in this case, I am
having a hard time understanding what the 7&4 are about in this function?
=IF(A1=2,REPLACE(B1,7,4,"6001"))
and what the 6 is about in this one? =IF(A1=2,REPLACE(B1,7,4,"6001"))
Thanks
Frank

If you are struggling to understand the syntax of an Excel function, type
its name into Excel's help facility. Every function [except DATEDIF()]
has an explanation, and usually examples, and pointers to related
functions.
--
David Biddulph

Sorry, I know I didn't explain correctly.Colums are full from A-G. I
don't understand where to put the function . I will have to read a some
more. I also do not understand what the numbers 6, and 7&4 have to do
with this? Ok I need to read a lot, lot more!
Thanks
Frank

message Perhaps:

C1: =IF(A1=2, LEFT(B1,6) & "6001", B1)

Another possibility is...

C1: =IF(A1=2,REPLACE(B1,7,4,"6001"))

Rick

 

[Frank]

OK Sorry I didn't see that. I do try to find the answer on my own, guess I
am not as good at it as others.
Frank

Did you look at help on the REPLACE function?

If you did, you would find this explanation of the numbers.

REPLACE(old_text,start_num,num_chars,new_text)

Old_text is text in which you want to replace some characters.

Start_num is the position of the character in old_text that you want to
replace with new_text.

Num_chars is the number of characters in old_text that you want REPLACE
to
replace with new_text.

New_text is the text that will replace characters in old_text.


Gord Dibben MS Excel MVP

I appreciate all the comments. I have much to learn. But in this case, I
am
having a hard time understanding what the 7&4 are about in this function?
=IF(A1=2,REPLACE(B1,7,4,"6001"))
and what the 6 is about in this one? =IF(A1=2,REPLACE(B1,7,4,"6001"))
Thanks
Frank

If you are struggling to understand the syntax of an Excel function,
type
its name into Excel's help facility. Every function [except DATEDIF()]
has an explanation, and usually examples, and pointers to related
functions.
--
David Biddulph

Sorry, I know I didn't explain correctly.Colums are full from A-G. I
don't understand where to put the function . I will have to read a some
more. I also do not understand what the numbers 6, and 7&4 have to do
with this? Ok I need to read a lot, lot more!
Thanks
Frank

message Perhaps:

C1: =IF(A1=2, LEFT(B1,6) & "6001", B1)

Another possibility is...

C1: =IF(A1=2,REPLACE(B1,7,4,"6001"))

Rick