# Sum Last Numbers In A String Of Data

Mar 31, 2014
I maintain a spreadsheet to track monthly sales of a few thousand items (see attached sample data). I'd like to have a formula that would sum only the last 12 months in the range of data. It would need to ignore all of the data before and the blank cells after the 12 months.

It's difficult to update the range each month for all of the products.

View 5 Replies
ADVERTISEMENT
Jun 11, 2008

I have a field that contains the following: 012100002030

I need a formula that will tell me whether or not the string contains a number higher than 2 or whether or not it contains more than one 2.

Examples:

001000002011

111111000022

401110000000

the first loan would not meet the criteria as it contains no number higher than 2 and only contains one 2.

the second loan would meet the criteria as it contains two 2's.

the 3rd loan would meet the criteria as it contains a number higher than 2.

View 9 Replies
View Related
Dec 3, 2009

I have data like follwing strings.

WAY-308312-1

WAY-521340-1

233-6767-676-6

First two strings are correct. If I get number like in third string first part of string then we have to move row to some other sheet.

The first part of string should have text like WAY-123-13-1.

I used Val function it checking first charector only. It the string is like 2A1A-243-233-2 the it is not checking.

View 14 Replies
View Related
Aug 25, 2009

I would like to extract the digits below in red. The variables are the digits in all of the sections vary from a minimum of one digit to the maximum of three digits.

111.222.333.444

View 2 Replies
View Related
Feb 1, 2012

I need a formula to extract the numbers from data in a cell:

Examples of data in cells:

G 622 ENTERTAINMENT ( I need to extract 622 )

D 6129 TOYS ( I need to extract 6129)

C 1039 Toddler TOYS ( I need to extract 1039 )

R 05 VEHICLES (I need to extract 5 )

All the cells have spaces before an alphabetical identifier, then a space ( it could be more than one in some cases ), then a number ( between 1 and 4 digits ), then a space and a description.

View 9 Replies
View Related
Nov 15, 2013

The table below is a portion of a calendar to track vacation and sick time. What I am trying to do is add all the hours of vacation ("V") and sick ("E") time that an employee has used in a cell at the end of each row which represents a month. So there are 31 cells that I need to look in and if it contains V... sum in the vacation cell. There may be a space after the letters and there may not be. They can also start with a decimal.

month

1

2

3

4

5

Vacation

Sick

January

E8

V8

V.5

V6.25

E .5

Sum of V (should be 14.25)

Sum of E

View 6 Replies
View Related
May 21, 2014

I am struggling with a problem which involves a column with both the text and nos in that. I want to calculate the following formula.

Total = the Sumproduct of the nos in the description*Length*Width*Height.

In the description column,

1) The nos will be varying i.e x*x or x*x*x or X*x*x*x*.

2) After the nos there will not be any data.

Example table:

Work Description

Length

Width

Height

Total

Parapet wall 2x4x8

12

2

4

6144

[Code] .......

Is there a way to achieve this, what is the formula for this ??

View 9 Replies
View Related
Jan 9, 2007

I am looking for formulae to identify the first 20 non-zero numbers from a string of alphanumerical and non alphanumeric characters

EG If column A any data such as:

123

0123

01(2)3

0.123

123abc

0123abc

01(2)3abc

0.123abc

a1b2c3

1a2b3c

Then the corresponding row for column B would return 123

Using help from my maths Prof and some IT bods, i have so-far come up with the array-formula:

=1*MID(A7,MATCH(FALSE,ISERROR(1/MID(A7,ROW(1:100),1)),0),20)

assuming that the total string is not more than 100 characters.

View 7 Replies
View Related
Apr 7, 2007

is there a way to have the number formatting in Text() variable? For instance, I want to reference a cell that can change between $1.00 and $10,000,000.00.

Let's say that in A3, the number turns out to be $10,000.00.

When I put in text(a3,"$0.00") it spits out $10000.00. But if I go the other route and go text(a3,"$000,000,000) it displays "$000,010,000.00".

View 9 Replies
View Related
Oct 20, 2008

I have a list of data, 7 coumns x 4000 rows with integers in them. b2:h4173

I would like to write a forumula(e) which will tell me which 3 integers appear in the same row most often.

View 9 Replies
View Related
Feb 6, 2009

I have not managed to find exactly what I am looking for so far..

I have cells with a mix of numbers and strings and I would like to extract certain numbers only.

I.E.

123a bcd8

acbd 1234e

I would like to only extract the first instance of numbers - so in example 1 - 123 and in example 2 - 1234. I have found various methods of extracting all numbers such as:

Function ExtractNums(r As String) As String

'Creates and returns a reference for a regular expression object

With CreateObject("vbscript.regexp")

'Sets the pattern to be non-digits

.Pattern = "D"

'We want to find all matching non-digits

.Global = True

'Replace all non-digits with an empty string and return this value for the function

ExtractNums = .Replace(r, "")

End With

End Function

View 9 Replies
View Related
Aug 11, 2009

I am trying to find numbers from a string. I have for example words "EUR Fwd 9x12" and "Eur Fwd 11x15" And i want to write a function that reads the first number from a string if there is only one number before "x" or two numbers if there are 2 numbers. So I have tried to build following function:

Function NumbersInString(Word As String) As Integer

Dim i As Integer

Dim FirstNumberInString As Integer, SecondNumberInString As Integer

For i = 1 To Len(Word)

If IsNumeric(Mid(Word, i, 1)) Then

FirstNumberInString = Mid(Word, i, 1)

If IsNumeric(Mid(Word, i + 1, 1)) = False Then

Exit Function

Else

SecondNumberInString = Mid(Word, i + 1, 1)

End If

End If

Next

NumbersInString = FirstNumberInString & SecondNumberInString

End Function

View 9 Replies
View Related
Jan 25, 2009

A given string of some letters (from three to five, six) is followed by a single number (1-4). Then it ends, or is followed by another such combination, up to a maximum of, let's say, four.

Example:

xxxx1

yyy3yyyy1

xyzxx1yxz4xzx1

xxx2xxx2xxx2xxx2

What I would like to do is to divide a given chain of characters into the smallest chains which contain only letters and one number. To the examples given above, I'd like to receive the following sets:

xxxx1

yyy3 and yyyy1

xyzxx1 and yxz4 and xzx1

... and so on

Is it somehow possible to do it with simple functions? Or is VB necessary (which sadly I don't know)?

View 6 Replies
View Related
Dec 26, 2009

I have textstrings which have numbers between questionmarks included which I want to have deleted.

Here is an example : This ?112? is text 12 and this must ?9? be deleted.

I want to have it as : This is text 12 and this must be deleted.

Is this possible with the 'Substitute' function or is there another way to do this?

View 4 Replies
View Related
May 11, 2010

In a data report i have a column which contains a mix of text and may contain an 8 digit number which could start in 0.

for example

in column A i could have "Hello how are you 01237232 I am fine"

I wish to extract the number into a seperate column, and would ordinarilly use a mid or left/ right function, however the text infront and behind the number will vary in length, which means i cannot do this.

The number will always be 8 digits, could start in 0 but will not always, and it may not be present in all the cells in this column.

How I an achieve this?

View 11 Replies
View Related
Jan 11, 2013

I have a list (SIC Codes) and I want to remove the numbers. The numbers range from 2 to 8 deep. The list exists in column B and I want the new list in column C.

01 Agricultural Production Crops

011 Cash Grains

0111 Wheat

0112 Rice

0115 Corn

0116 Soybeans

0119 Cash grains, nec

011901 Pea and bean farms (legumes)

01190101 Bean (dry field and seed) farm

01190102 Cowpea farm

01190103 Lentil farm

01190104 Mustard seed farm

View 3 Replies
View Related
Jan 11, 2013

I need to add 2 to 6 zeros to the end of string of numbers beginning in A2:

01

011

0111

0112

011901

The end result, all numbers must be 8 characters long.

View 2 Replies
View Related
Apr 21, 2013

i want Counting duplicate numbers in a string for example Counting 2

22241278-------- Count(2)=4

View 5 Replies
View Related
Sep 24, 2009

I have several lines with text strings containting three numbers in each line. I need a code to extract all three numbers from each text string. The numbers can be placed on the following columns in each row.

View 2 Replies
View Related
May 8, 2014

I've got some data which will look something like the following:

987249879238Steven1987dob98023498092384029834Tom1972dob298374928374928374987

I'm looking to remove any set of numbers more than 10 characters long. i.e. the desired output from the above would be:

Steven1987dobTom1972dob

View 4 Replies
View Related
Jun 30, 2014

I'm looking for a way to extract the data after the first space in a string to after the first set of numbers (always 8 digits). The formula I'm using is not accurate since the number of words that appears between the first space to the first set of numbers changes.

Sheet2ABC1Desired results2277 xxx spzrtmhk

companies 12345678 231516990 2475.06 3199.57 23.19 street 1 739.00xxx spzrtmhk

companies12345678 231516990xxx spzrtmhk companies 1234567831528 dts powertrain comp inc

15981808 231516989 43.55 84.40 48.40 5.00dts

[Code] ...........

View 9 Replies
View Related
May 30, 2002

Is there a way to separate text from numbers, (other than text to columns)

My data is all different lengths, nothing consistent,plus I want to put the separated data in another column.

View 9 Replies
View Related
Nov 17, 2006

I need to separate alphabets and numbers in a string ...

Example,

Hotel Crowne Plaza 675.00 USD

How can I read only the numbers (675.00) ???

View 9 Replies
View Related
Dec 4, 2008

Anyone know how to do this?

View 9 Replies
View Related
Jun 23, 2009

I have the below data to look at every day. The Data codes column A contains a part number and the Info Column B, the information about the part. I have in column C the serial numbers that change daily, I copy and paste this column in everyday.What I would like to do is search within the serial numbers for the data codes and return in the Result column D the info relating to the data code.

Data codesInfoSerial numberResult118Type A118F2041Type B118F BNG59617Type C2041F2151Type D2066F=9617570Type E2151F800Type FEXPEDITE COPY:570F=MEDIACOM PDF687Type GEXPEDITE:118F

Currently I spend hours removing all of the text and symbols (there are normally 2500 to 3000 serial numbers) and then do a simple vlookup. Is there a formula that could search the serial number to find the data code? Or some code I could use in a macro to do the job for me?

The serial numbers change daily but the data codes and info in the first 2 columns remain contstant - there are nearly 250 data codes.

View 9 Replies
View Related
May 30, 2002

Is there a way to separate text from numbers, (other than text to columns)

My data is all different lengths, nothing consistent,plus I want to put the separated data in another column.

View 9 Replies
View Related
Aug 23, 2008

I have list of 15000 of Alphanumeric data for ex. ADEDO125ADSD589ADF121,UIEIROIWS12556ERE545,ADAS15455212AD4564AD2

And so on. Now I want to extract word from this alphanumeric. And i want to use only formula not MACRO OR VBA

View 4 Replies
View Related
Feb 5, 2007

how do i count through a string of numbers until a specified number is found?

View 9 Replies
View Related
Jun 21, 2007

I have a woorkbook where I have a cell that has the following apperance:

12345678-ABCDEFG

The numbers can vary in length and as well as the text. What I am trying to do is to split this cell into to columns. Column 1 shows the account number, Column 2 should show the Text and the "-" sign should not be in any of the columns ie it should look something like this

Column1 Column2

12345678 ABCDEFG

I have found a useful function for the first part thanx to Mikerickson

URL:Extract Numerical Data

Function midNumber(inputStr As String) As Double

Dim i As Long

For i = 1 To Len(inputStr)

midNumber = CDbl(Val(Mid(inputStr, i)))

If midNumber <> 0 Then Exit Function

Next i

End Function

But how do i seperate the text and how do Crete a macro that would loop through some 2000 rows and create a column A and B from Column A?

View 3 Replies
View Related
Feb 19, 2014

I am trying to write a function that will give me the amount of time a certain number shows up in the same digit place in a large set of numbers.

For example:

111112221233313

111212222233323

111312223233333

111412224233343

111512225233353

111612226233363

111712227233373

111812228233383

111912229233393

given the number set how many times does the number 5 show up in the 4th digit. I know its 3 but for the data size I need to run it on, it is impossible to count.

What function would count how many times a certain number shows up in the same spot?

View 8 Replies
View Related