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.
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?
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
I am trying to do is extract the volume size of products in 'ml' from 10k plus products from a description field cell. this description field could also contain the weight of the product in grams so I cannot just do a search for a numeric string , it has to be associated with the milli-litres statement .
is is possible to do a sort of ' *ml ' search and then select and copy to another cell ???
I have a userform which has a textbox for employee id. If an employee enters less than 5 digits, it should prompt the user via msg box that he needs to enter 5 digits for employee id. However my coding doesnt work...can someone help me correct it?
I would like a function that checks an input number to see if it contains unique digits. If the digits are unique the output is 1 else the output is -1. Thus, supposing we call the function UNIQ(), we find UNIQ(15423) = 1 but UNIQ(154532) = -1. The input is always a positive integer.
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:-
in excel when im writing a vba macro is thier something that i specify to only find numerical, or only find alpha characters? what about in excel when im doing a odbc query?
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
Each field represents the number of "SALE QUANTITY" , "FREE GIVEN" , "RETURNED" products. We have got 34 PRODUCTS range. (34 x 3 fields = 102 textfields)
The name of the TextBox fields are as follows :
PR01S , PR01B , PR01K
PR02S , PR02B , PR02K
...
...
PR34S , PR34B , PR34K
All of the fields are required to be numeric data only. How can i check this fields to be numbers on the same time of the data input (or when jumping to the next field. There will be a message box mentioning that the field can only contain numbers + SetFocus to the wrong input field) ?
I dont want to write same code for each item, its too hard and long..
If i can solve this problem, then I will need help to enter the values from this fields to the worksheet because I must find a way to enter the values with a for next loop or something.. I dont want to do it one by one.
Here is how the form looks :
I dont know if there is a possibility to exclude the product to be entered in the worksheet if all 3 boxes are Zero (0) for that specific item.
Example : If all fields are "0" for item "Coke" then I dont want this products information entered in the excel worksheet.
In my column of text strings, I have a multiple format of strings make-up. Below is just one of them I have to check that the first 6 are digits from 0 to 9, and it is followed by a hypen. If condition is true, the first 6 digits is the output (ie. 345678).
However, if I were to continue doing this for other strings, I would soon run out of characters limit that is allowed in a cell. I wonder whether a formulae such as below is valid? Any suggestion or help for a shorter formula is very much appreciated.
Given 5 rows in column A, I have strings that has the word 'Qty' and some none. For any string that has the word 'Qty', I want the numeric value before it. For string that doesn't have the word 'Qty', I want it to be 1. My question is, how should I added the numeric string without a helper column?
Column A Desired Qty to Take
some string name and ended with (4 Qty) 4
some string name without the word Qty 1
[code]....
In the above example, I would like to have an answer in say cell B1 to be 15
IDMacro Out Put X11891189 cdecde CONFRCONFR 175175 D0101101
I ahve a sheet having two column one is "ID" and other is "Macro Out Put" as shown above , is that possible with VBA code that it give the same result as i shown above in column "Macro Out Put". for reference i also attch the file
It works if alphabetic and numbers are clustered together such as scs987 It works if alphabetic and numbers are not clustered together such as scs987dtg
Problem: It does not work when numbers are not clustered together such as scs987dtg1234
I need to be able to locate a date in a spreadsheet bassed uppon a user input date. I am looking for the week that contains the user's date. However it seems even if I initialize the value as Date, it still reads the user's input as a string.
Is there an easy way make sure the system automaticaly recognizes the user's input as a date, or is there a way to quicly convert the string to a date?
I'm trying to extract alphanumeric data before and after a numeric string. The numeric string is in the middle of a URL, which is a varying distance from the start and end.
Here's a sample of my data:
URL Page ID Headline Section quiz.impression-http://www.stuff.co.nz/2677193 2677193
I start with just column A and generate the other three from that.
I'm using some excellent code from a Mr Excel guru (thanks again Peter) to extract the 8-digit numeric string in column B, I just need C (after) and D (before).
That macro is: Dim Bits Dim c As Range Dim i As Long
For Each c In Range("A4", Range("A" & Rows.Count).End(xlUp))....................
I have a sizable spreadsheet, which has one column where the cells are mostly numeric strings of up to 6 numbers - there are variations, which I will mention.
What I need to do, is to treat the numbers as individual figures, and add them, then divide by the number of figures. e.g. cell value '123456' I need to process as (1+2+3+4+5+6)/6.
The variations are that '0' has to be added as 10, and any letters need to be added as 12. '-' and '/' which are the only non-alphanumeric values should be ignored.
I have created a macro that searches for an existing part in my worksheet and copies the found part to the row above. I would like to increment the value of cells B and H in the new copied row. Both cells contain an alphanumeric string that ends with a numeric value. For example cell B has a string like this "APL-DK0030" and cell H has a string like this "Dell Optiplex GX260 V09"....
Problem: String is broken into 2 parts within a delimeter. Goal: To keep each String/Segment intact regardless of Alpha/Numeric.
I have a .csv file with 26 segments (commas). This file contains customer data: PO#, Order#, Carton ID, Address 1, Address 2, Address 3, etc.
I've written code to capture all 26 segments as: Input #1, Seg1, Seg2, ... ,Seg26. The variables work fine until it encounter a string with Numeric first then Alpha within the same segment.
For this example we have a string such as (...,187 Main Street, PO Box 369,...) Seg4 = Address Seg5 = PO Box
The Segment Variables in Input #1, get shifted when it encounters the numeric portion of the address...so the result becomes: Segment4 = 187 Segment5 = Main Street Segment 6 = PO Box 369.
So on my next loop when ReadLine is executed...all the variables are shifted and I can't focus on the specific segment that I need to do something to it.
It delimits when it encounters Numeric then Alpha within the string, but accepts the entire string into the variable when its Alpha then Numeric within a delimeter.
How I can keep the entire string in tact within each delimeter?
