Function To Retrieve Only Alphanumeric Values
Oct 22, 2007
I have Column A sample below:
Column A
Row 1 WG32127
Row 2 WFH3040 / 3920
Row 3 WFBF3709/3919
Row 4 WG32156/3915, 3911, 3920
Row 5 WG32148129/3915, 2922
I'm trying to use the Left Function to get only the Alpha-Numeric codes only. I'm having the following problems.
1) The alpha-numeric codes are not a standard number of characters (they can vary anywhere from 4 to 10).
2) In some cases the alpha-numeric codes are alone in the column
3) In other cases they are with other numbers with back slashes separating them
4) In the cases where there are back slashes there are spaces, commas and other numbers as well.
Is there a way to use the Left Function to retrieve only Alpha-numeric codes only.
View 9 Replies
ADVERTISEMENT
Mar 2, 2010
A2 cell data = 11 digits alpha-numeric number.
* begin with either "0" or "1"
I want to the formula to look at the 6th and 6th digit (numeric, alpha-numeric, or Alpha) and compare it with the cells under "'PSCCR Queue - Manager" TAB from C4 thru C7.
Below formula is only looking at the first value when comparing.
=IF(A2="","",IF(ISNUMBER(MATCH(MID(A2,6,2),'PSCCR Queue - Manager'!$C$4:$C$7,0)),"ITS","Non ITS"))
View 9 Replies
View Related
Mar 5, 2009
how to apply this function to my sheet given below. In this budget sheet, I want to create a graph which would show all the items percentage (item by item) with the values given in columns C & H only, leaving out the lines which are empty or bear 0 values.
View 2 Replies
View Related
Mar 28, 2012
I have huge set of data listing almost 20 columns and 400 rows..i am trying to apply a formula for one of them..
Condition: Onshore or Offshore
Data to be retrieved: list of locations at onshore and offshore
Result expected: If a particular cell is selected as onshore then allow user to select only Onsite locations same for offshore
View 1 Replies
View Related
Jul 13, 2014
I am trying to use COUNTIF function to retrieve data to a "Report" tab on my workbook.I need it to be count if as I have 2 criteria whether or not the value in the cell should be counted toward the sum (one is text basically saying "Y" and other the date). Now the formula worked fine when use hard data such as this:
=COUNTIFS('Inspection Report'!$B$3:$B$1048576,"*" & "06/05/2014" & "*",'Inspection Report'!E3:E1048576,"Y")
OUTCOME: 47 (which is correct)
See how I used the date there, I typed it myself. However the end user should not be able to have access to the formula, so I used a cell with drop down list and the date is stored there. So following the logic, my new formula should be this:
=COUNTIFS('Inspection Report'!$B$3:$B$1048576,"*" & C2 & "*",'Inspection Report'!E3:E1048576,"Y")
OUTCOME: 0 (which is incorrect)
how the date is stored. By following my logic this should work as the cell only holds a string value and should be used like this in to calculate the result. if I need to use only the value of the cell in a formula like this should there be any conversion so excel understands what I am trying to do?
View 5 Replies
View Related
Dec 7, 2006
I have created a log file for work which records jobs processed. Additional code has just been added to retrieve job information data from a statistical process control program. This works fine however the spc program outputs the Ohmic value as ohms which I need to convert into alphanumeric format. For example: job value is 24K4, spc outputs as 24400 which I need to convert to 24K4. The Ohmic values range from mOhms to gOhms. Examples below.
0.003 = R003
0.025 = R025
0.150 = R15
1.0 = 1R0
15 = 15R
3600 = 3K6
1700000 = 1M7
220000000 = 220M
1000000000 = 1G0
Bearing in mind that the above are just examples of possible values, is it possible to convert numeric values to an alphanumeric format similar to those show above using VBA? You may ask why I need to convert. The log file allows manual input of job information where the value is entered in alphanumeric format. It also contains a search form which displays results allowing the user to see if a job has been processed. Obviously it is easier for the user if the job value is displayed as alphanumeric.
View 2 Replies
View Related
Feb 8, 2007
On a work sheet I have 20 columns that may or may not have data in it. In the 21 column I want to have a sum "of the values" in the other columns. Problem is the data is in this form i.e. 6 01/17/07 smr or 15 1/19/07 gtw or 24 01/21/07.
To clarify each cell will have a value of a number from 1 to 99 followed by 2 spaces. Then it can have 2 more spaces and an id tag or just finish with the date.
So, from the example above in column 21 I would have a current value of 45 and it could change if more values were added.
Note values are generated from a macro. It may be possible to change the macro so that a comma or such may be used to seperate the different "values / data types"
View 4 Replies
View Related
Mar 26, 2009
I have 2 sheets recording feedback and summarising the overall percentages for each trainer.
My boss wants me to do this by month!?!?!
I have created a third sheet but I am not sure how to do the following:
Look up the trainer name
Look up the month
identify the percentages for each category
create an overall average of these percentages
So for example if Jon smith trained twice in JAN getting 100% and 50% in cat1, it would display 75% in the cat 1 cell and so on.
View 7 Replies
View Related
Dec 1, 2008
I have a custom report on one tab called "Professional Services" and a list of invoices on another tab called "invoices". I need to pull the invoices from the invoice tab and put them on to the Professional Services tab in the appropriate place on the report.
For example, I need to pull back all invoices for center 820202, with the budget line name Controller Team Contractor. I want the Vendor name in column B and the Amount in column H. Here's another catch. If there is more than one "Vendor" with the same name, I want the Amount to sum. Make sense?
I thought maybe putting the invoice list in to a pivot table would be the answer but I can't get the vendor name from the pivot table.
View 8 Replies
View Related
Jun 23, 2009
I need to automatically populate a column with an alphanumeric MFR0001 to MFR9999 and am currently using the formula below to add 1 to the start point (A1)...
A1=MFR0001
Formula in A2 =LEFT(C1,3)&(RIGHT(C1,4)+1)
This works perfectly if I start at MFR1000, but breaks and drops the 000 if I use it on the number I need to start with (MFR0001).
View 3 Replies
View Related
Jan 3, 2010
I have declared a number of string variables named "hlpYear1", "hlpYear2" etc in the 'Worksheet_SelectionChange' function. "hlpYear1" contains the appropriate text to display if the selected language is English, "hlpYear2" is for French and so on. I was hoping that I would be able to use VBA to display the appropriate text by way of a 'For..Next' statement along the following lines:
View 4 Replies
View Related
Jan 30, 2014
I am trying to retrieve values with INDEX and MATCH. I have column B that contains "i", and "m".
I have formulas for the first occurence of "m"
=(INDEX(NUP!$J:$J,MATCH("m",NUP!$B:$B,0)))
Which will look for the first "m" and output the value in column J that aligns with the "m"
How would I alter this or a new formula for the nth occurrence of "m"?
View 5 Replies
View Related
Nov 10, 2009
I am wondering if you can sum the numeric parts of a range of cells containing alphanumeric values. Here is an example:
values to sum: 500, a20, ab30
expected results: 500 + 20 + 30 = 550.
In this project, the number of values to sum is large and I do not wish to use long formulas to extract the numeric part of each cell within the range. I am also forbidden to use up extra cells as an intermediate working out. Can you help?
View 10 Replies
View Related
Aug 3, 2012
Any code that can extract the alpha numeric values from sample spreadsheet below?
Where in col A "SCn" is extracted (or copied) and then pasted in col H?
n = 1 to 99
That is - from this:
A
B
C
D
E
F
G
H
I
J
K
L
[Code] .........
To this:
A
B
C
D
E
F
G
H
I
J
K
L
[Code] ......
There are thousands of rows to extract the alpha numeric values from.
View 6 Replies
View Related
May 9, 2014
I have a data set of students results and I have been using countif to pickup similar grades eg, C1, C2 and C3 all as C. However I need to subtotal filter against teachers and the sumproduct won't let me use C* as a wildcard to pick up the three types of C. How I can subtoal countif multiple criteria.
View 3 Replies
View Related
Feb 16, 2005
I have two columns of data: 1 column can be possibly anything, either numeric, alphanumeric, or alpha only (VINS or vehicle numbers). The adjacent column are four different types of vehicles. I've tried concatentating the two to try and count the unique combinations (a truck maybe used more than once during the day), which I can get unique values, but not unique values for each type. Also, the range sizes are variable, but no more that 65 rows.
An example:
5CZ975 Van
25RW04 Straight
5CZ975 Van
ZJ6026 Van
So, the result I am after is 2 vans, 1 straight truck
I found an array in a another post, but it is not working properly (see cell F7 in the attached):
=sum(if(frequency(if($b$2:$b$18=$f7,$a$2:$a$18),$a$2:$a$18)<>"",1,0))
**ctrl+shift+enter**
View 5 Replies
View Related
Sep 26, 2013
I need to make an IF statement that when it's true it need to retrieve the values from a different column. With what function is it possible to just retrieves values?
I need to return the value from pivot table. Forgot to mention this.
View 4 Replies
View Related
Sep 15, 2012
I'm trying to make this piece of code work to no avail yet... Every time I run it, the compiler pops up with Error #9: "Subscript out of range". The range consists of hundreds of cells in 1 column with geographic coordinates formatted as "General". Like this: myArr(38.91472, 37.20318, 38.63824, etc.). Tried to re-format the cells to "Number" - doesn't work either.
Code:
Sub searchArray()
Dim myArr() As Variant
Dim i As Integer
myArr = ThisWorkbook.Worksheets("Sheet1").Range("G1:G8594").Value
For i = LBound(myArr) To UBound(myArr)
Debug.Print myArr(i)
Next i
End Sub
View 2 Replies
View Related
May 13, 2014
I have 2 columns i want to find out which items match in each column and put the matching value in column c. I have tried Vlookup and continue to get an N/A .. I tried countif and I get either an N/A or a value error. I have tried turning the cells into text but that is not working either..
column A has about 1700 rows and column B has about 4000
MOST CELLS ARE 6-7 VARIABLES.. satrting with either 01, 02, 03 with 4-5 letters following or have a 6 digit number or 6 letter value.
View 1 Replies
View Related
Apr 30, 2014
I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese
John Chinese - Simplified
John Korean
Martin Arabic
Martin Chinese - Simplified
Martin Russian
Ramon Arabic
Ramon Russian
Sam Arabic
Sam Chinese- Traditional
View 1 Replies
View Related
Sep 24, 2007
i have a column with the following values...45, 60, 35, 20, 10, 35, 28, & 17. in the next column, i want to put a "1" next to the values listed (45, 60, etc.) if their sum is less than 100...the formula needs to be flexible enough so that it does not sum any values that another "1" above it has already summed.
using the values above, a "1" would be placed next to 45, 35, and 28.
View 9 Replies
View Related
Feb 1, 2010
I'm from Macedonia, we have to values for VAT, 18% and 5%. I try to separate values that have 18% VAT and 5% VAT. I'm using IF function but not is that I need
I'd like to SUM all cell from H2 to H24 that have 18% VAT in H27 and in G27 I'd like to SUM all cells that have 5% from H2 to H24.
View 3 Replies
View Related
Mar 26, 2014
In my spreadsheet, one cell in every row has a string that contains the invoice number. I would like to get the invoice number in a cell of it's own. The problem is that different vendors use different lengths of characters for their inv #, so I can't do a one size fits all MID formula.
What would be the best way of getting the invoice number?
View 9 Replies
View Related
Sep 16, 2008
I have a series of subs that pass variables but at one point the value gets lost.
I want to pass the value in variable "PremFundType0 " to the function "MonthlyProcessing" and have it assigned to "PremFundType". But the problem is that PremFundType loses its value in the next function.
HTML Public PremFundType As Integer
Public PremFundType0 As Integer
Public J As Integer
Public m As Integer
Sub Main()
PremFundType0 = 12
Call MonthlyProcessing(120, PremFundType0)
End Sub
Sub MonthlyProcessing(proj_yrs, PremFundType)
J = 1: m = 1
For J = 1 To (proj_yrs)
For m = 1 To 12
Call BOM_Charges..........
View 9 Replies
View Related
Oct 30, 2007
I have a row of values and I need to pull the second non-zero value on another sheet, which in the example below is $850.
$0 $0 $0 $285 $850 $278 $481
I use the Index/Match forumla below to grab the first non-zero value ($285)
{=+INDEX($A$1:$G$7,MATCH(TRUE,$A$1:$G$7>0,0))}
This formula works great to retrieve the first non-zero value.
How can I modify this formula to grab the second non-zero value ($850)? Then how do I modify it again to grab the third non-zero value ($278)? Then how do I modify to grab the fourth non-zero value ($481)?
I don't want to use IF/Then statement as that might be too long. I think Index/Match would do the trick, but I am missing something.
View 4 Replies
View Related
Aug 4, 2009
i have thousands of data for me to look at. its something like this. based on the sample attached file, in Sheet2, in the yellow highlighted cell, i need to display the value based on B1 from Sheet1.
in the file, i want to retrieve c34's value in col B. there are several c34 in col A but all the values belongs to 1 in col B. i want to be able to take in c34's col B value that is, 1 and not the sum. i keep trying sumif formulae but it gives me a 0.
View 2 Replies
View Related
Jan 25, 2013
How can I obtain the array values from a function?
For example: Those steps were made by cytop to split a text (btw it works perfecet!!), I just added Function splitText(strSplit As Variant) As Variant ... End Function
VB:
Function splitText(strSplit As Variant) As Variant
Dim datosColumnaIncio() As Variant
Dim iTemp As Integer
Redim datosColumnaIncio(Len(strSplit) - 1)
For iTemp = 1 To Len(strSplit)
datosColumnaIncio(iTemp - 1) = Mid$(strSplit, iTemp, 1)
Next
End Function
So I want to know if its correct like that:
VB:
Function splitText(strSplit As Variant) As Variant
Dim datosColumnaIncio() As Variant
Dim iTemp As Integer
[Code] .....
So when I call the function:
VB : MsgBox splitText("F4")
It throws an error message :s what is wrong or how can I get the array values of a function ???
View 2 Replies
View Related
Jul 4, 2014
I have 3 conditions within my spreadsheet, which only 1 condition can have a numeric value in it at any one time.
The result of my calculations returns the following results
Condition 1: False; Condition 2: 168001.5072; Condition 3: False
I then use the MAX command to pick up the cell that has a value in it.
The command I use is: =MAX(L28,N28,P28)
The problem occurs is when I use this MAX command it Rounds up the value from 168001.5072 to 168001.51 - I need to stop this rounding from occurring.
Now I have just been told on this forum about how to use the TRUNC command to stop my original formula from rounding my final value (168001.5072) but I'm not sure if using the TRUNC command will work in this situation.
I can not use the "=" command becuase depending on the values I use any 3 of the conditions can be met, which will mean a value will sit in either condition 1, 2 or 3, so that is why I need to pull the MAX value.
The attached image shows my issue.
The value on the right 168001.5072 is being rounded up and displayed as seen on the left as value 168001.51 and I would like ot know how to stop this from occuring.
image2.PNG
View 4 Replies
View Related
Jan 7, 2014
a column contains dates in date format, contains a date before the column b date b column contains today() I want c column to get some output, if b-a is more than 5 years (exact days), say 20 do , if b-a is more than 2 years and less than 5 years, say 40 do , if b-a is less than 2 years , say 60 otherwise blank use if condition to get the above results. I have formatted a, b with date formats
View 9 Replies
View Related
Sep 6, 2008
Is there a way to plot a function, for example y = a + x*b or y = e^x in a graph without it having x and y values in cells of the spreadsheet?
That is, in y = a + x*b, for example, what I'd like to do is have an input cell for a and b, but no cells that show a value of y for every corresponding value of x , and a graph on a chart showing what the function looks like. Thus, the graph would only rely on inputs of a and b and on nothing else. A program called MathCAD does this, but I'm not sure how to do this in excel.
View 14 Replies
View Related
