Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find All Numbers and Text in a cell | Excel Discussion (Misc queries) | |||
Find All Numbers and Text in a cell | Excel Discussion (Misc queries) | |||
Find $ in a string of text and return numbers | Excel Discussion (Misc queries) | |||
Find text numbers | Excel Programming | |||
Find text numbers | Excel Programming |