find numbers in text and put value in another worksheet
 

Is it possible to write visual basic code that will search the text in a
cell, find the numbers in that text, add them up, and put the total into a
cell on another worksheet, in a cell corresponding to a number in a different
cell on the first page?

sheet 1.
col A. contains numbers. column E, G and I contain text (but not always)

sheet 2.
col A contains numbers, columns ab, ac & ad need to have the total of the
numbers found in the text in sheet 1 E,G or I respectively. When the number
in col A matches.
sheet:1
A j k l
258 2 moml 6 vgml
537 1 sfml 2 moml

results should be on sheet:2

A ab ac ad
258 8
519
253
537 3

Any help please, as I am struggling to learn visual basic on my own.

find numbers in text and put value in another worksheet
 

How a bout a formula

=IF(Sheet1!A1=a1,SUM(IF(ISNUMBER(--MID(Sheet1L1,ROW(INDIRECT("1:"&LEN(Sheet1L1))),1)) ,--(MID(Sheet1L1,ROW(INDIRECT("1:"&LEN(Sheet1L1))),1) ))),"")

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"thomsonpa" wrote in message
...
Is it possible to write visual basic code that will search the text in a
cell, find the numbers in that text, add them up, and put the total into a
cell on another worksheet, in a cell corresponding to a number in a
different
cell on the first page?

sheet 1.
col A. contains numbers. column E, G and I contain text (but not always)

sheet 2.
col A contains numbers, columns ab, ac & ad need to have the total of the
numbers found in the text in sheet 1 E,G or I respectively. When the
number
in col A matches.
sheet:1
A j k l
258 2 moml 6 vgml
537 1 sfml 2 moml

results should be on sheet:2

A ab ac ad
258 8
519
253
537 3

Any help please, as I am struggling to learn visual basic on my own.

find numbers in text and put value in another worksheet
 

Hi Bob,
Many thanks, but I was looking for visual basic code rather than a formula,
as the sheets that will contain the information will be copied into the
workbook and thus the formulas will not stay there.

"Bob Phillips" wrote:

How a bout a formula

=IF(Sheet1!A1=a1,SUM(IF(ISNUMBER(--MID(Sheet1L1,ROW(INDIRECT("1:"&LEN(Sheet1L1))),1)) ,--(MID(Sheet1L1,ROW(INDIRECT("1:"&LEN(Sheet1L1))),1) ))),"")

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"thomsonpa" wrote in message
...
Is it possible to write visual basic code that will search the text in a
cell, find the numbers in that text, add them up, and put the total into a
cell on another worksheet, in a cell corresponding to a number in a
different
cell on the first page?

sheet 1.
col A. contains numbers. column E, G and I contain text (but not always)

sheet 2.
col A contains numbers, columns ab, ac & ad need to have the total of the
numbers found in the text in sheet 1 E,G or I respectively. When the
number
in col A matches.
sheet:1
A j k l
258 2 moml 6 vgml
537 1 sfml 2 moml

results should be on sheet:2

A ab ac ad
258 8
519
253
537 3

Any help please, as I am struggling to learn visual basic on my own.