remove specific content of a cell to a new cell

[pkinghan]

hi guys

First time here!

I have text data in a cell e.g. "Quantities: 2" or "Quantities: 3" etc. I want to be able to sum the fact that total quantity is 5 and I would normally do this by using text to column feature (fixed width) however is there a way I can extract or move just the number into a new column so i can total that new column?
Thanks

P

 

[Joe4]

Welcome to the Board!

Do you mean that you want to drop the "Quantities:" part altogether?
In using Text to Columns, you can choose "Do Not Import" for the first field. It will then drop that and just leave the number.

 

[AlanY]

you can try the text-to-columns built-in function use : as delimiter

 

[pkinghan]

thanks! Yeah bit more complex than that. For example is there a way to edit out text and just left number in a column?

 

[pkinghan]

thanks guy! really appreciate your quick responses!

thanks! Yeah bit more complex than that. For example is there a way to edit out text and just left number in a column?

 

[gaz_chops]

You could sum it as it is

=SUMPRODUCT((REPLACE(A1:A4,1,12,""))*1)

[COLOR=#000000][FONT=-webkit-standard][TABLE="width: 133"]
<colgroup><col width="133" style="width: 100pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 133"]Quantities: 2[/TD]
[/TR]
[TR]
[TD="class: xl63"]Quantities: 3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Quantities: 14[/TD]
[/TR]
[TR]
[TD="class: xl63"]Quantities: 5[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[/TR]
</tbody>[/TABLE]
[/FONT][/COLOR]

 

[pkinghan]

Sorry I'm not being very clear at all! see below...essentially i have a column with hundreds of these records as text and I want to be able to 'extract' or just count the numbers in there? I'm assuming text to columns is the way to go and the quickest way but not sure.

<colgroup><col></colgroup><tbody>
</tbody>

 

[gaz_chops]

if you just need to sum the values, try my suggestion.

 

[pkinghan]

thanks gaz! only seeing this now! I'll give it a go

 

[pkinghan]

Hi Gaz - I used this formula
=SUMPRODUCT((REPLACE(G5:G15,1,44,""))*1) as I assume the 1,44 is the amount of characters and spaces before the number? However I am getting error