Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel formula help?
I am new to setting up Excel to fill out columns for me and need some =
help, please? Not even sure if I need a macro or what. I have a column of data in the format(s) #LL#### or ##LL####. I need to pick up the first number(s), eliminate the Letters, then pick = up the next 2 #'s, then change the last 2#'s to 0's (in a new column). Can anyone please give me some help on where I start or what I need to = do? TIA CKL |
#2
|
|||
|
|||
If there are either 7 or 8 characters always you can use a simple IF
function =IF(LEN(A1)=7,100*(LEFT(A1,1)&MID(A1,4,2)),100*(LE FT(A1,2)&MID(A1,5,2))) -- Regards, Peo Sjoblom (No private emails please) "CKL" wrote in message ... I am new to setting up Excel to fill out columns for me and need some help, please? Not even sure if I need a macro or what. I have a column of data in the format(s) #LL#### or ##LL####. I need to pick up the first number(s), eliminate the Letters, then pick up the next 2 #'s, then change the last 2#'s to 0's (in a new column). Can anyone please give me some help on where I start or what I need to do? TIA CKL |
#3
|
|||
|
|||
Thank You for your input Peo!
However, when I input that statement, I get the return #VALUE! Unfortunately, I don't know enough to know where the problem may be. "Peo Sjoblom" wrote in message = ... | If there are either 7 or 8 characters always you can use a simple IF=20 | function |=20 |=20 | = =3DIF(LEN(A1)=3D7,100*(LEFT(A1,1)&MID(A1,4,2)),100 *(LEFT(A1,2)&MID(A1,5,2= ))) |=20 |=20 |=20 | --=20 | Regards, |=20 | Peo Sjoblom |=20 | (No private emails please) |=20 |=20 | "CKL" wrote in message=20 | ... | I am new to setting up Excel to fill out columns for me and need some = help,=20 | please? Not even sure if I need a macro or what. | I have a column of data in the format(s) | #LL#### or | ##LL####. |=20 | I need to pick up the first number(s), eliminate the Letters, then = pick up=20 | the next 2 #'s, then change the last 2#'s to 0's (in a new column). |=20 | Can anyone please give me some help on where I start or what I need to = do? |=20 | TIA | CKL=20 | |
#4
|
|||
|
|||
Another way:
=SUBSTITUTE(SUBSTITUTE(UPPER(A1),"LL",""),RIGHT(A1 ,2),"00")*1 Biff "CKL" wrote in message ... I am new to setting up Excel to fill out columns for me and need some help, please? Not even sure if I need a macro or what. I have a column of data in the format(s) #LL#### or ##LL####. I need to pick up the first number(s), eliminate the Letters, then pick up the next 2 #'s, then change the last 2#'s to 0's (in a new column). Can anyone please give me some help on where I start or what I need to do? TIA CKL |
#5
|
|||
|
|||
Duh! Thanks so much! It worked just fine (just didn't edit the cell =
location correctly!) (and more than that - I actually understand (most of) it! <g It has = been over 30 years since I used IF/THEN statements!) "Peo Sjoblom" wrote in message = ... | If there are either 7 or 8 characters always you can use a simple IF=20 | function |=20 |=20 | = =3DIF(LEN(A1)=3D7,100*(LEFT(A1,1)&MID(A1,4,2)),100 *(LEFT(A1,2)&MID(A1,5,2= ))) |=20 |=20 |=20 | --=20 | Regards, |=20 | Peo Sjoblom |=20 | (No private emails please) |=20 |=20 | "CKL" wrote in message=20 | ... | I am new to setting up Excel to fill out columns for me and need some = help,=20 | please? Not even sure if I need a macro or what. | I have a column of data in the format(s) | #LL#### or | ##LL####. |=20 | I need to pick up the first number(s), eliminate the Letters, then = pick up=20 | the next 2 #'s, then change the last 2#'s to 0's (in a new column). |=20 | Can anyone please give me some help on where I start or what I need to = do? |=20 | TIA | CKL=20 | |
#6
|
|||
|
|||
Thanks Biff! Although not as familiar looking...... (read - "new" to me =
<g"), it makes sense..... I will take a look at this too - just to learn! "Biff" wrote in message = ... | Another way: |=20 | =3DSUBSTITUTE(SUBSTITUTE(UPPER(A1),"LL",""),RIGHT( A1,2),"00")*1 |=20 | Biff |=20 | "CKL" wrote in message=20 | ... | I am new to setting up Excel to fill out columns for me and need some = help,=20 | please? Not even sure if I need a macro or what. | I have a column of data in the format(s) | #LL#### or | ##LL####. |=20 | I need to pick up the first number(s), eliminate the Letters, then = pick up=20 | the next 2 #'s, then change the last 2#'s to 0's (in a new column). |=20 | Can anyone please give me some help on where I start or what I need to = do? |=20 | TIA | CKL=20 |=20 | |
#7
|
|||
|
|||
Hi!
If the "LL" will always be in uppercase as in your example, you can make the formula a little shorter: =SUBSTITUTE(SUBSTITUTE(A1,"LL",""),RIGHT(A1,2),"00 ")*1 Biff "CKL" wrote in message ... Thanks Biff! Although not as familiar looking...... (read - "new" to me <g"), it makes sense..... I will take a look at this too - just to learn! "Biff" wrote in message ... | Another way: | | =SUBSTITUTE(SUBSTITUTE(UPPER(A1),"LL",""),RIGHT(A1 ,2),"00")*1 | | Biff | | "CKL" wrote in message | ... | I am new to setting up Excel to fill out columns for me and need some help, | please? Not even sure if I need a macro or what. | I have a column of data in the format(s) | #LL#### or | ##LL####. | | I need to pick up the first number(s), eliminate the Letters, then pick up | the next 2 #'s, then change the last 2#'s to 0's (in a new column). | | Can anyone please give me some help on where I start or what I need to do? | | TIA | CKL | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does Excel show a formula in ONE cell ? | Excel Discussion (Misc queries) | |||
Excel CHALLENGE...3 questions(indirect,rank,array formula)... | Excel Worksheet Functions | |||
converting formula from lotus.123 to excel | Excel Worksheet Functions | |||
How do I get the formula bar in excel 2003 | Excel Discussion (Misc queries) | |||
Suddenly Excel can't calculate formula!!! | Excel Worksheet Functions |