help column

[Guest]

hi all,
is there a way to avoid greating a help column (to strip the numbers from
column A) as in my case where i have a range containing items like
A1=2000 name1
A2=1500 name2
A3=700 otherName
A4=500 name1
A5=""
A6=1500 name1
.....
now i want to sumproduct for ( name1) only.
many thanks for any help

 

[Ken Wright]

Assuming example given is representative of all records:-

=SUMPRODUCT(--(LEFT(A2:A2000,FIND(" ",A2:A2000)-1)),--(MID(A2:A2000,FIND("
",A2:A2000)+1,LEN(A2:A2000))="name1"))

or switch name1 for a cell reference, BUT, its very bad practice indeed to
store numeric data in the same place as textual data and do what you are
doing. Far far better to use something like Data / Text To Columns and
split them up into two separate columns.

 

[Guest]

Thanks Ken Wright, your formula is of great help
but how about if the " " is not found in the empty cells of the range?
i hope you can help
with many thanks again

 

[Fred Smith]

Ken already answered that question. Use Text to Columns.