Retreiving number from numbers and text.

[Russmeister57]

Hi All,

I'm new to this group and I was wondering if somebody could help me.
I'm learning all aspects of excel, macros and VBA, not anywhere near being good at it yet but I'm struggling with this macro.

I've got a list of names and numbers, eg:

Adam 8
Dave 8
Susan 6
Andrew 4

I need to be able to pick up just the numbers out of this list of text and digits. Can somebody help me with this please?

Thank you

 

[Fishboy]

Hi Russmeister57, welcome to the boards.

Can you elaborate a little on what you are trying to do? Are you looking for VBA or a formula? Do you literally just need to recreate the list with only the numbers?

If it is the latter the fastest way I found to just get the numbers was to copy the list to a new column, select that column then do a standard Find / Replace for "* " and replace it with nothing. This stripped out the space and everything preceding it and left only the numbers. It is possible however that this is not how you want to go about it.

 

[Rick Rothstein]

Hi All,

I'm new to this group and I was wondering if somebody could help me.
I'm learning all aspects of excel, macros and VBA, not anywhere near being good at it yet but I'm struggling with this macro.

I've got a list of names and numbers, eg:

Adam 8
Dave 8
Susan 6
Andrew 4

I need to be able to pick up just the numbers out of this list of text and digits. Can somebody help me with this please?

If the number is always last, then this should work for you...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99),99))

 

[Sixthsense]

In A1 Cell

Adam 8

<colgroup><col width="64"></colgroup><tbody>
</tbody>

In B1 Cell

=IFERROR(MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),255),"")

 

[henrik2h]

If it is only 1 number you could use

=right(a1;1)

or if it is up to two numbers and there is always a space between the name and the number

=--right(a1;2)

 

[Russmeister57]

Hi Russmeister57, welcome to the boards.

Can you elaborate a little on what you are trying to do? Are you looking for VBA or a formula? Do you literally just need to recreate the list with only the numbers?

If it is the latter the fastest way I found to just get the numbers was to copy the list to a new column, select that column then do a standard Find / Replace for "* " and replace it with nothing. This stripped out the space and everything preceding it and left only the numbers. It is possible however that this is not how you want to go about it.

Sorry all, maybe not clear enough.

Its a complete list of names like the initial post. I've create a button and I want to assign a macro to it that just takes the number that is next to the name, Adam 8, in this case just the 8 to appear in A1 for example.

Adam 8
Dave 8
Susan 6
Andrew 4

I want A1 to =26.....8+8+6+4.

 

[Rick Rothstein]

Sorry all, maybe not clear enough.

Its a complete list of names like the initial post. I've create a button and I want to assign a macro to it that just takes the number that is next to the name, Adam 8, in this case just the 8 to appear in A1 for example.

Adam 8
Dave 8
Susan 6
Andrew 4

I want A1 to =26.....8+8+6+4.

Well, that is still not completely clear... I cannot tell from your post if you want a macro to physically alter your list so that only the numbers remain in their cells or you want a macro that will calculate the sum of the numbers from the cells... in either case it is confusing because the list apparently starts in cell A1 and you are saying you want the sum to be in cell A1.

Let's assume the list is in cells A1:A4 and that you do not want to physically change the values in the list and that you will put the sum somewhere other than cell A1... are you aware that you can get the sum you are after without using a macro? This will do it...

=SUMPRODUCT(0+TRIM(LEFT(RIGHT(SUBSTITUTE(A1:A4," ",REPT(" ",99)),99),99)))

 

[Russmeister57]

That does exactly what I want thank you so much