Extract 2nd Number From A String
Feb 15, 2013
I wanted to return the second number in a string such as this:"0 of 0 jobs ... View all jobs"
The following worked perfectly:=MID(U3,FIND("f", U3)+1,LEN(U3)-FIND("f", U3)-23)
Until this line came up:"1 of 1 job ... View all jobs"
Obviously the "jobs" changed to "job" because of there being only 1, so the "-23" no longer worked. I need this to get the second number out of the string and it can change because the data is being refreshed from a website so it could easily change from one line to another.
View 3 Replies
ADVERTISEMENT
Jun 20, 2014
It's for listing items to eBay. Ink Toners to be precise, the macro I have creates everything I need for a listing with a few msg prompts and importing data from another sheet. Saves me hours of work. Each cartridge has a Black, Cyan, Magenta, Yellow. The macro works fine for cartridge sets that go under one name. E.g: Brother TN325 Black, TN325 Cyan, TN325 Yellow... etc.
I've run into an issue with some cartridges which have different names: HP C530a Black, C531a Cyan, C532a Yellow...
I set the macro up to deal with different number, by removing the last character of the "tonername" string "C530", then replacing it with "C531".. etc.
What I haven't factored in is the ones with an A on the end. I need a way to tell it to ignore the a if there is one.
To make it clear, I will be inputting the text: C530a From this, I need to create 3 more strings: C531a, c532a and C533a
Here's the snippet of code I'm using to achieve what I'm already doing.
Code:
Selection.Replace What:=A, Replacement:=Left(y, Len(y) - 1) & "1"
Selection.Replace What:=b, Replacement:=Left(y, Len(y) - 1) & "2"
Selection.Replace What:=c, Replacement:=Left(y, Len(y) - 1) & "3"
[Code]....
View 1 Replies
View Related
Apr 28, 2014
Below is a sample of data I need to extract the 8 digit number:
WO_32092_56228491_575482-113476-WP55
WO_32092_56228491_115130-WP55
WO_32092_56228491_115116-115118/115117-WP55
WO_32092_56228491_291881-318903-WP55
WO_259_56748761
WO_13895_52245652COUNTYRDN-30
WO_17368_51421730-A-ADDPOLEINFIELD
WO_17368_50885431-A-ADDMISSINGPOLE
WO_11021_52350485brock3377
In a spreadsheet, I had set up three columns where:
A: removed first three characters. WO_32092_56228491_115130-WP55 to 32092_56228491_115130-WP55
B: removed up to the _ . 32092_56228491_115130-WP55 to 56228491_115130-WP55
C: captured the first 8 characters left. 56228491_115130-WP55 to 56228491
I am pulling the data into Excel via an ODBC where there is thousands of rows of data. The three column process puts a tremendous strain on the processor.
Is there a formula that will extract the 8 character number without a three step process?
View 3 Replies
View Related
Dec 16, 2008
I would like to parse a part number.
ab24s-12-g4a
if there is a "-#-" in the string, that is the value I want. If the "-#-" is not present, I would like to take the first number. Currently, as written in your first example, I am getting the first number - excellent and thank you!
In the sku, the first digit reprepresents capacity. If a second number is present after the capacity portion of the sku (i.e. "-#-"), that means that they want to order it filled with less than capacity. For instance, a carton of eggs and they want 6 eggs instead of the max capacity of 12. (i.e. egg12-6-z2z). Numbers after the second octet (i.e. z2z) do not matter.
It would be a number only in the second octet. If there are characters with it, the equation should disregard it.
Here are the scenarios, and what the formula should accomplish
1) egg12 --> currently returns 12
2) egg12-6 --> would return 6
3) egg12-6-g2c --> would return 6
4) egg12-g2c --> would return 12
View 5 Replies
View Related
Oct 11, 2011
Have many rows of alphanumeric strings. 2-4 letters followed by a variety of numbers, some spaces, and some additional letters. What I need is to return the first number after the initial 2-4 letters, everything after that first digit is junk. Thus, I would like a formula that takes me from:
AB23400 12 -> 2
CET25800 1 -> 2
NYT67300 H01 -> 6
HSRF49900 1 -> 4
View 9 Replies
View Related
Jan 12, 2009
I need to extract a set of 5 numbers from a string of characters. The number set I need is always 5 digits long, always starts with a 1, and always begins after a period. But the number I need can occur in different parts of the string:
In each example below I would want to extract only the 10572 portion.
5194003.10572.N
5194003.10572.R
5194003.1.0010785.10572
7500008.N.5999900.10572
Additionally (if possible) if the cell is blank I don't want anything to show up, and if the string begins with a letter then I don't want anything to show up either.
View 9 Replies
View Related
Dec 3, 2009
My knowledge of functions is pretty limited, or negligible to be honest. I require a function to extract numbers from each cell in a selected range.
Eg.
cells contain the following data:
cell 1: xxxxxx 45,59
cell 2: x xxxxx xx 6,45,27
cell 3: x xxxx 28
were x represents text
I need to loop through each cell and extract each of the numbers and list them in a separate range. If applied to the above range of cells the function would show the below in the ouput range. Each number in its own cell.
6
27
28
45
45
59
View 9 Replies
View Related
Nov 7, 2008
Say I have a string "09800EBHR052708"
How would I take the first 5 characters and get a number out of them?
View 14 Replies
View Related
Jan 20, 2008
What is the formula to be used to extract number from a alphanumeric string located at different row?
example:
test123test128test131
test124test129test132
test125test130test133
test126test131test134
test127test132test135
test128test133test136
result: ....
View 11 Replies
View Related
Sep 26, 2009
I am trying to extract a 7-consecutive-digit number from a mixed string (letters and numbers). The only condition is that the number has to start with a "7" or "6" and be exactly 7 consecutive numbers long. My function only evaluates for one and one within the other but not both. It's faulted because it only looks for the occurrance of "6". rCell is the string range.
Function ExtractNumber(rCell As Range)
If Len(Mid(rCell, InStr(1, rCell, "6"))) > 6 Then
ExtractInvoice = Mid(rCell, InStr(1, rCell, "6"), 7)
ElseIf Len(Mid(rCell, InStr(1, rCell, "7"))) > 6 Then
ExtractInvoice = Mid(rCell, InStr(1, rCell, "7"), 7)
End If
End Function
View 9 Replies
View Related
Jan 15, 2008
I am using a regular expression in a function to extract numbers from strings. the code I am using is below.
Function ExtractNumber(rCell As String)
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Pattern = "d*.d*"
If RegEx.Test(rCell) Then
ExtractNumber = CDbl(RegEx.Execute(rCell)(0))
Else
ExtractNumber = vbNullString
End If
End Function...
View 3 Replies
View Related
Jan 25, 2007
Imagine that I have this list of thousands of customers, who are listed in column B in the format "customer no+space+customer name".
Now I want to extract ONLY the customer no from the text string in column B and insert it as number value in column A on the same row.
Would have been easy with "Left" command, but as the customer numbers have variable length, I have a problem.
View 5 Replies
View Related
Aug 14, 2014
I'm trying to extract all the numbers from the left of the delimiter ":" . They can occur once or more.
E.g. cell F2 contains BP2.2.1:40 BP2.2.1:50 BP2.2.1:60 BP2.2.1:70 BP2.4.1:80
and what i want as a result is : BP2.2.1 BP2.2.1 BP2.2.1 BP2.2.1 BP2.4.1.
I've tried =IFERROR(LEFT(F2,FIND(":",F2)-2), " ") but only displays 1 out of a possible 4 in the cell.
View 12 Replies
View Related
Feb 19, 2014
I need a VBA function to extract number sequences from a string and separate them with hyphens In the example below cell A1 has the value 'xx2 yyy34 zz515' The code must produce the value '2-34-515' from the above example I have the following function that extracts the numbers but need a way to separate the groups with a hyphen
Code:
Function parseNum(strSearch As String) As String
Dim i As Integer, tempVal As String
For i = 1 To Len(strSearch)
[Code]....
View 9 Replies
View Related
Sep 4, 2012
I am working with flight numbers and want to split out the letters from the digits. Examples,
BA1234A
BA123
EZY4566H
BE7893B
In column A I need the first two or three letters only,
BA
BA
EZY
BE
In column B I need everything to the right of what appears in column A
1234A
123
4566H
893B
etc.
View 7 Replies
View Related
Dec 22, 2013
I have the following type of info in A1,A2,A3...
Code:
nameGaryage40cityPittsburgheight190
age30height150
ameLindacityMichigan
citySan Jose
ameHarryheight180age50
My goal is to get as close as possible to this,so it will be easier to sort and manage
Code:
nameGaryage40cityPittsburgheight190
nameLindaage30cityMichiganheight150
nameHarryage50citySan Joseheight180
I can't use the "" sign as delimiter to separate them into different columns because the age,city,name and height fields are in random positions on different cells.The good thing is person's name will always come after "name" string, age is alwals followed by "age" string, so it cannot be like nameheight40Michigan180
I think the following would be the easiest method(not for me tho).If on B1 I had a formula that said "find the string "name" and write anything after it until you reach the next "" character".On C1 field I could have a formula "find the string "age" and write anything after it until you reach the next "" character.On D1 I would have the same for "height" string,then on E1 for city string.
My question is somewhat similar to this one
Extract A String Between Two Characters
Formula which outputs the data between 3rd and 4th instances of the "_" character.Can we substitute "3rd and 4th" with a specific strings like "age" or "height" ?
Code:
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"_","|",3))+1,LEN(A1)),"_",REPT(" ",LEN(A1))),LEN(A1)))
View 3 Replies
View Related
Aug 23, 2007
I have a problems here. The problems is attached in the file. I wanna extract alpha/char from a string. Example: I wanna extract the words "(M)" with the bracket from the string "Toothbrush (M)" in column A. After extracting the (M) out, I wanna do a validation to compare the (M) in column B with another data in column C, if the (M) is same as the data called "Medium" in column c, the validation will return "Match" in the column d!
View 9 Replies
View Related
May 16, 2013
I have a string that follows this format: SAMSUNG CT797F-1
From this string I need to extract 797F only. Further complicating the problem is that its not always SAMSUNG it could be ABB CT797F-1 or KOMSATISU CT797F-1
I have implemented a function that can extract all the numbers, but can't think of how to extract 797F from the various different strings.
View 6 Replies
View Related
Nov 28, 2006
I have a list of names in a data set like the examle below. I would like to be able to isolate the last name so I can match the data in this data set with a data set with additional information. The only common set of data I have is the first and last names.
View 9 Replies
View Related
Apr 10, 2007
I need to extract the term between the two colons: Snowboard Jackets
This term resides in AB289-
Snow : Snowboard Jackets : Guys SoftShell
Using MID, but not able to get all the term
View 9 Replies
View Related
Aug 14, 2014
Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?
Example;
Cell A1 has 123XVF1234
Cell A2 has 321AFW4321
In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value
Example;
If B1 returns 643562 it would need to be able to be converted back to 123XVF1234
View 7 Replies
View Related
Apr 7, 2014
In sheet1 I have a simple database consisting of 5 columns of data
Column A : Name ie James Jones
Column B : payroll number ie 123456
Column C : shift times ie 1245-2124
Column D : job title ie floor
Column E : comments ie A/L or 0600-1500
what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.
View 4 Replies
View Related
Feb 9, 2014
I need to extract the month and day from a date / time string into another cell. An example of the string is 1/28/2014 10:26
I want the result to be 1/28
View 4 Replies
View Related
Aug 20, 2009
In the following rows I want to extract the string that ends in K (ie everything after the final space). But I don't know the starting position or how long the string will be.
C2 9K
C1M (RB57) 4K
C2 4K
C6 FM (RB78) 120K
C2 9K
C1M (RB57) 3.5K
C2 (RB68) 12K
Can this be done?
View 8 Replies
View Related
Sep 7, 2009
I have a string (as below - Call them A1:A4) which I would like to seperate into 4 columns (Call them B1:E4).
I have successfully seperated the first part using MID (It's always 5 digits) but the second part has a varing length which then impacts on the third and fourth parts of the string.... Any ideas?
87261 WIMBLEDON 10:08 10:10
87169 NEWMALDEN PASS 10:13
87171 SURBITON PASS 10:15
87177 HMPTNCTJN PASS 10:16
To add to this I am using the POCKET PC version of Excel which does not have all functions so at the moment I am limited to which functions I can use (Can you add functions to the PPC?).
View 6 Replies
View Related
Dec 10, 2007
I need to be able extract a string from within a longer string. The information I want will always start 5 chracters in and be bordered by an underscore either side, but could be of any length. For example in the following string, I would want to extract WF602.
DDR_WF602_____02_00_001_FLANGE_WR34_4_HOLE_PLAIN_CLEARANCE
I've been looking at the RegExp functions but can envisage problems with this as later parts of the string (WR34) have the same form as the part I need.
I can get rid of the first 4 characters using
View 9 Replies
View Related
May 14, 2009
I've this question, how do i extract a part of the text using wildcard(example "ID=") in the string in the whole columns and copy to other columns or delete the unwanted.
Below is a example which i create for better understanding.
View 11 Replies
View Related
Jul 2, 2010
What I want to do is extract the date only from a string of text in a cell and put it in the cell next to it. The date is not alway in the same position within the text string and although similiar, the text string can differ. There are around 6000 lines in the sheet, I've pasted 4 as an example below.
THE BIG COMPANY LTD 15312178 COLLECTION 14.12.09
The Small Company Ltd 19073864 COLLECTION
DATE 17/05/10 511438 511437 511436 ORD LE
The Medium Size Company 19080948 COLLECTING WEDS 26/05/10
The Very Good Company 19149554 COLL DATE:28.06.2010 175 PEA PARK ROAD Z7 9AB
View 9 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
Apr 23, 2012
I am trying to extract the ounces (OZ) data from a string: example, BOX 15OZ 1819106287, CONTAINER 12.3OZ 1818176234. I need everything from OZ prior until there is a space.
I.e.
15OZ
12.3OZ
View 3 Replies
View Related
