Excel formula help?

C

CKL

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
 
P

Peo Sjoblom

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*(LEFT(A1,2)&MID(A1,5,2)))



--
Regards,

Peo Sjoblom

(No private emails please)


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
 
C

CKL

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.


| 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*(LEFT(A1,2)&MID(A1,5,2)))
|
|
|
| --
| Regards,
|
| Peo Sjoblom
|
| (No private emails please)
|
|
| | 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
|
 
B

Biff

Another way:

=SUBSTITUTE(SUBSTITUTE(UPPER(A1),"LL",""),RIGHT(A1,2),"00")*1

Biff

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
 
C

CKL

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!)

| 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*(LEFT(A1,2)&MID(A1,5,2)))
|
|
|
| --
| Regards,
|
| Peo Sjoblom
|
| (No private emails please)
|
|
| | 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
|
 
C

CKL

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!

| Another way:
|
| =SUBSTITUTE(SUBSTITUTE(UPPER(A1),"LL",""),RIGHT(A1,2),"00")*1
|
| Biff
|
| | 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
|
|
 
B

Biff

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

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!

| Another way:
|
| =SUBSTITUTE(SUBSTITUTE(UPPER(A1),"LL",""),RIGHT(A1,2),"00")*1
|
| Biff
|
| | 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
|
|
 
Top