Hi, Is there way to looking in a column for a number or number +
wildard, and replacing it with just a number?

for example, suppose you have a list of number(s) or letter(s) with or
without wildcards

like this: on Column "D"
1*
2*
3*
w*

you like to just remove the astricks (*) with just the number or
letter. by using Vlookup into another cell for example in Cell "A5",
but not replacing the actaully column that has the data.

can this be done? i really appericated the help, thanks a bunch,..

Hi

If you just want text then if a2 = 1* then LEFT(A2,Len(A2)-1) returns 1 as
text.

To convert "1" to a number, and ignore text "w" use

=IF(ISNUMBER(LEFT($A2,LEN($A2)-1)*1),LEFT(A2,LEN(A2)-1)*1,LEFT(A2,LEN(A2)-1))

Regards
Peter
" wrote:

Hi, Is there way to looking in a column for a number or number +
wildard, and replacing it with just a number?

for example, suppose you have a list of number(s) or letter(s) with or
without wildcards

like this: on Column "D"
1*
2*
3*
w*

you like to just remove the astricks (*) with just the number or
letter. by using Vlookup into another cell for example in Cell "A5",
but not replacing the actaully column that has the data.

can this be done? i really appericated the help, thanks a bunch,..

If you want to lookup the leftmost number in a column of strings and return
only that leftmost number, why you already had that number to begin
with......no need to "look it up"..........perhaps I'm missing something
here....could you please elaborate more clearly as to exactly what you have
and what you wish to achieve?

Vaya con Dios,
Chuck, CABGx3





" wrote:

Hi, Is there way to looking in a column for a number or number +
wildard, and replacing it with just a number?

for example, suppose you have a list of number(s) or letter(s) with or
without wildcards

like this: on Column "D"
1*
2*
3*
w*

you like to just remove the astricks (*) with just the number or
letter. by using Vlookup into another cell for example in Cell "A5",
but not replacing the actaully column that has the data.

can this be done? i really appericated the help, thanks a bunch,..

your missing something
I try explain ir another way, first its very complex...
anyways, On one sheet we call the sheet "hitters stats"
on the hitters stats sheets it has numbers ranging from 1, 8 and then
"1 to 8" with one wild card the astrick ( * )
so that next set of numbers look like this
5*
until it reaches 8*
also it has two other letters one with just a "w " and one with a (w*)

so it becomes like this the column
1
2
3
4
5
6
7
8
1*
2*
3*
4*
5*
6*
7*
8*
w
w*

now that the complete list of numbers and letters combin, however its
mix in one column going from row 2 to around 650 rows down..
i need to import it that column but to convert it if it has either w or
w" or 1* to 8" to just either number or zero

I know how to lookup it, and do nesting and such, i just dont know how
to search for the column and replace my results in another sheet to
work in another formulas.I am thinking of it can be done with vlookup,
with if statement and using some kind of wild cards. or is there
another way of doing this..



CLR wrote:
If you want to lookup the leftmost number in a column of strings and return
only that leftmost number, why you already had that number to begin
with......no need to "look it up"..........perhaps I'm missing something
here....could you please elaborate more clearly as to exactly what you have
and what you wish to achieve?

Vaya con Dios,
Chuck, CABGx3





" wrote:

Hi, Is there way to looking in a column for a number or number +
wildard, and replacing it with just a number?

for example, suppose you have a list of number(s) or letter(s) with or
without wildcards

like this: on Column "D"
1*
2*
3*
w*

you like to just remove the astricks (*) with just the number or
letter. by using Vlookup into another cell for example in Cell "A5",
but not replacing the actaully column that has the data.

can this be done? i really appericated the help, thanks a bunch,..

It's still not real clear to me (I haven't had my medication yet today
<g)......but if you're trying to just extract the leftmost character from
strings, then you could use
=LEFT(A1,1), copied down, or another method would be Data TextToColumns
Fixed and set the column break after the first character..........If that's
not it, perhaps you could send a copy of the file to my home addy and I'll
take a look tonight.......croberts at tampabay dot rr dot com..........


hth
Vaya con Dios,
Chuck, CABGx3



" wrote:


your missing something
I try explain ir another way, first its very complex...
anyways, On one sheet we call the sheet "hitters stats"
on the hitters stats sheets it has numbers ranging from 1, 8 and then
"1 to 8" with one wild card the astrick ( * )
so that next set of numbers look like this
5*
until it reaches 8*
also it has two other letters one with just a "w " and one with a (w*)

so it becomes like this the column
1
2
3
4
5
6
7
8
1*
2*
3*
4*
5*
6*
7*
8*
w
w*

now that the complete list of numbers and letters combin, however its
mix in one column going from row 2 to around 650 rows down..
i need to import it that column but to convert it if it has either w or
w" or 1* to 8" to just either number or zero

I know how to lookup it, and do nesting and such, i just dont know how
to search for the column and replace my results in another sheet to
work in another formulas.I am thinking of it can be done with vlookup,
with if statement and using some kind of wild cards. or is there
another way of doing this..



CLR wrote:
If you want to lookup the leftmost number in a column of strings and return
only that leftmost number, why you already had that number to begin
with......no need to "look it up"..........perhaps I'm missing something
here....could you please elaborate more clearly as to exactly what you have
and what you wish to achieve?

Vaya con Dios,
Chuck, CABGx3





" wrote:

Hi, Is there way to looking in a column for a number or number +
wildard, and replacing it with just a number?

for example, suppose you have a list of number(s) or letter(s) with or
without wildcards

like this: on Column "D"
1*
2*
3*
w*

you like to just remove the astricks (*) with just the number or
letter. by using Vlookup into another cell for example in Cell "A5",
but not replacing the actaully column that has the data.

can this be done? i really appericated the help, thanks a bunch,..