UDF To Extract Digits From Variable Range String

Apr 28, 2014

I have a long string that has a list of digits in the middle that I want to extract. The string is variable length and the number of digits I want to extract is 5-7. I also have slashes in between the numbers I want to extract. I need a UDF that allows me to extract the 5-7 digit number from the string and restrict around the slashes (i.e. if two sets of digits in the string match the condition for extraction, extract the one around the slashes.) For example my original data is like

1. aaa/12345/aaa/123
2. aaaaa/123456/aaaaaa/3423
3. 323/aa/1234567/aa

and I want for results

1. 12345
2. 123456
3. 1234567

Is there a UDF that allows me to do this?

How To Extract Variable Length String

Feb 9, 2014

i just wanna extract the date shown(last data until meet the first comma) in first column to another column.

column 1
abcde, 1234
abcd, 12345
abcdef, 123

column 2
1234
12345
123

Extract Number Of Variable Length From Text String

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.

Formula To Extract Variable Substring From String With Multiple Spaces?

Jul 19, 2012

I've found several posts about returning variable substrings that dealt with one or two spaces, but I have not been able to find anything that is for multiple spaces. The number of characters before the substring will remain constant.

For example:

Income from transmission agreement - 83 subs @ \$0.44

In this case I need to extract: 83

Income from transmission agreement - 10,312 subs @ \$0.50

Need: 10,312

String Variable To Call Defined Range Variable

Nov 10, 2006

Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?

I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.

Extract Strings From Range Of Variable Length Text

Jan 18, 2008

I have the following issue. I have a list of strings. Each string contains certain characters that are exactly the same for all strings, some characters are different making the string longer (in some cases). What I need to do is extract some combination of characters from each string. The strings look like this:

AB & CDE & FG & I mmmm yyyy.HIJK
AB & CDE & FG & II mmmm yyyy.HIJK

The part "AB & CDE & FG & " (incl. spaces) is the same for each string. The next part contains a roman count from I to VII, causing the length of each string to vary. The "mmmm" part contains the current month spelled in full e.g. December. This part differs as well, for each string. The "yyyy" part contains the year in four digits, e.g. 2007. The part after the dot is the same for each string again.

What I need to do:
- is to extract the month and assign it to a new string
- extract the year and assign it to a new string
- extract the roman number, translate it to a normal number (II -> 2) and assign to a string (or integer).

Setting A Range Using String Variable

Jan 8, 2014

I'm having trouble setting the Range "UtilizationRange" using a variable, "CurrentRange" to store that range. I get the error "Run-Time error 1004: Method 'Range' of object'_Global' failed".The rest of my code works when I set the utilization range using the actual range for my purposes, but the problem is the range will change each month and I don't want to change it manually each time. There's a cell in the excel sheet this refers to (AG3) that holds the range value, which in this case is [N7:N75], and I would just like to return that value to the variable "CurrentRange" and set UtilizationRange equal to that.

The value is returned to CurrentRange, but it's the last line that's giving me the problem.

Sub RangeTest()Dim UtilizationRange As Range, Cell As RangeDim CurrentRange As String
CurrentRange = Range("AG3").Value MsgBox (CurrentRange)
'This returns the value [N7:N75], which is what I want
Set UtilizationRange = Range("CurrentRange")End Sub

Sorting Range Based On String Variable

Jul 31, 2014

I have a spreadsheet with column headers like, "Date" "Phone number" "Name" ect., and rows of data below. Each of these rows contains data pertaining to a prospect employee, as the purpose of the spreadsheet is to keep track of job applicants. One column is used as a 'Recruiter Sign-off' area, where the recruiter who processed the application inputs their name:

Example.png

I'm trying to automate a variation of excels sort function. However, I don't want to sort 'From A to Z.' Instead, I want to have the rows of data sorted with the 'Recruiter Sign-off' column used as the sort Key, and a selected name (of one of the recruiters) used as the sort Criteria.

I already have a userform that allows the user to select a name from a listbox. When they press confirm, the name is stored in a Public String variable, (selectedRecruiter). I'd like then like to have the rows below the column headers to be sorted based on the selected name. Example: User selects the name "Jon" from a list. The application then brings all of the applicants that 'Jon' has signed off on (those rows that contain his name) to the top rows of the range.

The workbook is shared, so I cannot use tables.

I found a code that accomplished what I want, however it also leads to a horrible system crash after a few times running it (BEX crash).

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

Naming Range With Integer Or String Variable?

Mar 13, 2012

I am trying to name a Range in VBA with the following Case statement.

Select Case counter
Case "2": Set Wk(numWeeks) = Range("P1:Q136")

I have many more cases and have defined Wk(5) as a Range. What I am really after it is possible to to name the range using something similar to the code above that uses a predefined variable in the naming process.

Extract Two Digits From Number

Oct 18, 2006

I want to extract the fifth and sixth digits from an 8 figure number i.e. 20010891 would equal 10.

I have tried every combination of Left & Right combined with Find that I know and nothing works.

Extract Digits From Text

Nov 13, 2006

I have a string in a cell that contains a name, a 3-4 character alpanumeric code and then a 4-6 digit number, each separated by a space. An example or two:

J Bloggs SPEC 123456
J Smith AG06 9364
J & K Brown SWP 358686

I need to be able to have in a new cell the 4-6 digit number at the right of the cell. give a formula that will separate the data I need? I've tried numerous ways without success.

Extract Numbers Based On First 2 Digits

May 27, 2008

I would like to extract numbers based on the first 2 digits (22....) of a column along with its next column (on the right) and put them on another sheet.

Regular Expressions, Extract DIGITS

Sep 27, 2006

I need to extract DIGITS from following: C23, C24, C515-C519. It may be done with regular expressions. Does anybody have experience with VBA code for RegEx?

Input: C23, C24, C515, C519.
Output: 23,24, 515, 519

Extract Digits From A Single Long Number

Oct 17, 2011

I have in my cell a number, namely, 5260007005020024100055040300004110000000

What I would like to do is extract a set of digits from it,

Starting from the second 2, and shown here in the dots. I need all of the numbers in a separate cell, "52600070050200 .24100055.. 040300004110000000"

Hope this is clear. bearing in mind the number will remain as one so I would need to start at 14 then using LEN or something I'm not sure, extract the following 8 into another cell.

Extract Cells With Largest Number Of Digits From A Column

Mar 29, 2014

I basically I have a column with numbers. All the numbers are positive integers. What I like to do is have a VBA function that extracts the integers with the largest number of digits. So for example if we have the following column:

12

123

234

12346

2345

[code].....

So basically we search for largest number of digits, and extract the numbers that fit this category, which could be just one number or multiple numbers.

Check If String Contains Only Digits

Apr 19, 2008

I'm working on a project where I need to prevent the user from entering non-digit string inputs. I've tried the using IsNumeric(MyString). but that doesn't prevent , + - signs to be entered. I also had to conditionally enable the input of a string with leading zeros like "01" so what I've ended up with is a rather messy looking' sub that handles this (don't know if forum rules allows me to show it here?). The problem is that I'm not a 100% sure that my sub fit to handle all eventualities so therefore my question is whether there is an easier, fool proof way to check my strings?

Calculate Sum Of Digits In A Number String

Feb 22, 2010

I need to calculate the sum of digits in a numeric string. The function should keep on adding the digits of each result, till the final sum is a single digit. Please refer to the following example and result expected:-

Supposed the number entered in Cell A1 is 456789

Result 1 = 4+5+6+7+8+9 = 39

Result 2 = 3+9 = 12

Final Result required = 1+2 = 3

Check If First 5 Digits Of String Are Numeric

May 23, 2007

I have some code where I need to check if the first five char are numbers and not letters.

I have in a column for example
12345-someone is here
23456-someone else is here
someone is here too

I need to get all of the ones that have 5 digits and not pull in the other into a new list.

I have tried Left(CPHierAll. Cells(CPHierAllRow, 1),5) which will get me the fist 5 char. and then i need to check to make sure that they are all numbers and not char.

if Left(CPHierAll.Cells(CPHierAllRow, 1),5) = "#####" then
But this does not bring anything in.

I also tried if CPHierAll.Cells(CPHierAllRow, 1),5) = "#####" then

I have 3 column that i am checking for different thinks the first two work just find and seperate out on the check but the last one with the numbers is being a pain.

Vba Calculator: Click Add After Inputting The Digits Just To Have It Added To He Variable, Which I Just Add Up At The End

Jan 29, 2007

i have 1 - 9 number buttons, the +, - , * , / , and a C for clear, as well as an exit button. the display is a label. i have to click add after inputting the digits just to have it added to he variable, which i just add up at the end... terrible.

Option Compare Database
Option Explicit
Dim var1 As String
Dim var2 As String
Dim first As String
Dim second As String
Dim sign As String
Form.Caption = "this calculator lab sucks."
Me.lbDisplay.Caption = ""
End Sub
' on clear
Private Sub cmdClear_Click()
Me.lbDisplay.Caption = ""
var1 = ""
var2 = ""
End Sub.............

Regular Expression: Check If A String Has A Sequence Of 6 Decimal Digits

Apr 13, 2007

I am using the following to check if a string has a sequence of 6 decimal digits in it. But am getting an error. If(str Like *######*). I have to check if str has values like 123456USA ; ABC725439 ; jh658478hd. I thought # represents a single digit and * represents any no of characters

String Extract - Separate First Two Or Three Letters In A String

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.

Extract String Between Characters After Specific String

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)))

Extract Alpha From A String And Compare With Another String

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!

Inputbox Value Can Be Compared To A String Variable Or A Numeric Variable At The Same Time

Dec 7, 2008

I am trying to develope a "goto" page macro where the page value maybe 1,34,7A, 256C etc. I am not clear on how an inputbox value can be compared to a string variable or a numeric variable at the same time. This is what I have done, but when the texboxvalue is "7A" it doesn't work.

Insert String And String Variable Into Cell

Feb 18, 2014

I'm using a userform to create a new sheet. The form already creates the sheet and names it what was typed into the userform. Now I want it to place that variable in a cell along with a string. the following code will place the variable from the form (tbname) into cell b5.

Number Formatting: The First Three Digits Will Be Separated And Then Subsequently 2 Digits

Oct 31, 2008

i need to format my numbers in the following format

10,00,000.00

the first three digits will be separated and then subsequently 2 digits

VBA - Extract Value From A String

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.

Extract Last Name From A Name String

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.

Use Mid To Extract String

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

Sum Up The Digits Within A Range Of Cells

Nov 28, 2008

I am looking for a formula to sum up all the digit(s) within a range of cells, e.g.