how to pick the numeric part out from a cell?
e.g. range("a1").value= 384HK
in range("b1"), i would like to have only 384, ignoring the HK
any excel tools or VBA code can accomplish this? there is a bunch of data that comes like this and i just dont have time to retype only the number one by one
How do I copy the numeric part(11 Digits) of a cell
EG. A1 = "GL CREDIT DDA07 60291627599 ZFT"
I Require "60291627599" in A2
Descr
ANSWER
GL CREDIT 62180577391@DDA ZFT
62180577391
GL CREDIT 62300192854 62300192854 DDA06 ZFT
62300192854
GL CREDIT ACC REC 62273991367 ZFT
62273991367
The numeric 11 digit number is not always in the same position in the cell.
How do I copy the numeric part (11 Digits) of a cell ,
E.G. A1 = "GL CREDIT DDA07 60291627599 ZFT" in only want "60291627599" in A2
Descr
ANSWER
GL CREDIT 62180577391@DDA ZFT
62180577391
GL CREDIT 62300192854 62300192854 DDA06 ZFT
62300192854
GL CREDIT ACC REC 62273991367 ZFT
62273991367
11 digit not always in same position in cell.
I have a string that with spaces in between.
example:
X3PP2 1200 U
I would like to extract the "1200". How can i do this in VBA?
Extract Numeric Part From The String End with a macro, that J8 will turn 8, and R4 will turn 4.
View 9 Replies View RelatedI have a cell with a value of, Text 1 and in the next field I want it to display Text 2, then text 3 and so on.
Is there any way of doing this? I guess I am looking for something like a1+1, just a shame it doesn't work.
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 simple program that picks up an ID on sheet1, finds that ID on sheet2, picks up the information in one cell from sheet2, and brings that information back to sheet1.
The program works fine until for all IDs above row 570. After row 569 something strang happens. The programs is still able to find the ID but when it tries to pick up the one cell value it is saying it is blank yet you can physically see the data is present.
In all my years of VBA programming I have never encountered anything like this. Especially, where things work fine on half the worksheet and not on the other.
I am posting the code below, but I don't actually believe it is in the code. I cannot post the workbook because of the information it contains...
I am trying to get excel to pick a cell from a hiden matrix and showing it on my worksheet. I want to pick the cell using 2 drop down boxes as bellow - 1 drop down picks option on X axis and the second picks using the Y axis and then the coresponding cell in the matrix is displayedMatrix example [URL] Drop downs example [URL]
View 4 Replies View RelatedI have cell A1 and it has the following data "-110 or -99%".
I want a formula that will pick up the percentage numbers only, IE -99%. Please keep in mind that the percentages change.
It can be 10000%, 0%, -500000% etc.
Can there be a formula that will pick up whatever is before or from the right?
I got some data like this
A1=CTAC US
A2=FCOB US
A3=TW US
The gap between each word and the word "US" is uneven. sometimes it's 3 spacebar apart, and sometimes 4.
I am trying the pick the part not including US. I tried =IF(ISERROR(SEARCH("US",A1)),A1,LEFT(A1,SEARCH("US",A1)-1)). It works but when I use match function to look up the word. It returns N.A. As the output is not just CTAC(4 characters), but 5 characters(including blank). The problem is the gap between between the word and "US" is different for each cell. so I cannot just minus the same character in the above formula (e.g. -2 if there is one blank cell). Anyone can tell me how to pick out the word and with only the number of characters in the word?(no blank)
ending of this to get the value of a cell that is on the the same line(volatile) but in column BA
TextBox1.Value = ActiveWorkbook.Sheets(ComboBox7.Text).Range(ComboBox6.Value)
Where combobox6.value is the row number that I want but column BA is the cell
I have a column of several thousand entries listed as numeric with a scientific symbol eg.
1.4mSv
19.53mSv/1mSv (some have a mix and or alpha/numeric range)
I want to convert them to the numeric value only. I'm extracting to a chart which is not recognising the alpha and throwing the data out. I tried find and replace, trying various options within the 'replace format' tab with no joy.
I have a few thousand products codes (i.e ABCD123BLA08 or SHU267BLA) They are non standardised in length or structure. I wish to pull out everything upto the end of the third numeric digit.( ie ABCD123 and SHU267) I cannot use left as they are all different lengths. Ideally i would serach for a non numeric char after the number then use left up to that point. Search can't do this, FIND can't do this.
Summary
ABCD123BLA08 would be ABCD123
SHU246BLU would be SHU246
I147ORT08-12 would be I147
I'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.
=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})
so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.
I have a column that has cells with values as
19352510
C084111X AA
24253081
A001290U AA
19599291
48413321
I want to write an If Statement is a column next to each entry that denotes two options either Broker or Agent. Where the code is say 19352510 then Broker and where it is say C084111X AA then Agent.
Broker codes will never contain a letter. The Agent code will always start and finish with a letter.
Using the below formula. I know that I should get the result of 32 if I am dong this correctly (182 matches part 1 of which 32 match in part 2).
=SUMPRODUCT(--($A$1='7. PM BDE'! $J$3:$J$366))*(--('7. PM BDE'!$L$3:$L$366=1))
Is there any way to remove the first part of a string of text in a cell and save the second part?
The first part of the text string is a team code that has a variable number of numbers, capital letters and sometimes spaces. The second part of the text string is a variable number of words in a team name that all start with a capital letter and have lower case letters. Every line has a different team code and team name.
The original spreadsheet also has a column with just team code. Is there a way of using this column to "subtract" the team code from the text string to just leave the team name?
cell A1 is equals to B1 because the cell C1 contains the number 1.
Now if i change the value of the C1 to 2 how can i make the A1 shows the value of the B2 ?
Just like. [A1]=B(C1-content)
I have a sheet (example attached) I need a formula to recognise only the numeric values either by automatically deleting the words or by entering the numbers in another corresponding sheet, either would do.
View 5 Replies View RelatedI am trying to delete a row if the cell value of column A is a number and not a name.
What i have been using to remove things is this.
Code:
Last = Cells(Rows.Count, "A").End(xlUp).Row
For G = Last To 1 Step -1
If (Cells(G, "A").Value) = "" Then
[Code].....
I have the following issue:
=AR85*36*75*6.29
The above formula does not return any value and it simply appears as it is keyed. Cell AR85 has 5. I know it is something to with this cell, but I cannot fix it. In fact the whole Column AR has these values downloaded. I tried "text to columns" but it still doesn't work.
I have compiled data from accross my branches, where in a 14 digit voucher number has been fed into the system along with data. The problem is that there is no unique format as this voucher number is fed in free format area.
.
i need to extract this consignment number from the cell value. for example,
A10 Contains "voucher No 12345678901234 paid"
A11 Contains "spl cons 15454563218921 thru person"
A12 Contains "incurred for recurring exp 45678932145826"
.
writing a macro to extract this 14 digit voucher number.
.
Sub Test2()
'From the top down
If IsEmpty(Range("A1")) Then
Range("A1").Select
Else
Range("A1").End(xlDown).Offset(1, 0).Select
End If
End Sub
I would like to change this to find the first non-numeric cell (technically the column i'm searching is dates, but i assume those are seen as being numerical). I tried to change this line:
If IsEmpty(Range("A1")) Then
TO
If IsNumeric(Range("A1")) Then
But as many of you know it didn't work (i also tried variations of IsNumeric(Range("A1")).Value=True
I have a sheet where i have many differently named areas (like state1_1 and state1_2) When I doubleclick on a cell then a macro should run with following criteria: 1) Macro will run if the doubleclicked cell is part of any range in the list. Here I mean that names of ranges which belong to that list start with word state (like state1_1 and state1_2). No other ranges should not be in that list. If the cell is not in the range that is part of the list, then nothing should happen.
View 2 Replies View RelatedThe purpose of the attached workbook is to calculate the average number of days a customer takes to pay their invoices. There are two tabs - Invoices and Payments. I am trying to calculate the number of days between Invoices "Sales Date" and Payments "Settlement Date".
The Payments tab is raw data exported from Attache financial system as CSV. It is the best I can get out of the system but the columns are all over the place.
I thought maybe from the Invoices tab I could search a row on the Payments tab to match the invoice number, find the cell containing the phrase "Invoice Number" then pick up the date from the following cell which contains the Settlement Date . This would be straight forward if the data from the CSV file consistently landed in the same column in the Payments worksheet, but it doesn't. As you can see, the phrase "Invoice Number" might appear in any one of several different columns.
I work in school which for example offers 20 courses for every student. Therefore I have a spreadsheet which has students name in A and B then the 20 courses spread from C to V.
Each student will study 3, 4 or 5 of the courses and will have value in the corresponding column (their teaching group) the rest of their row will be blank.
For each student create in columns X, Y , Z, AA and AB I would like to be able to pull from the 20 courses those which the student studies. Therefore finishing up with C to V hidden and just the student names and the 3, 4 or 5 courses they study.
Any ideas? if one method is to get them into one cell how do I get them into the 5 columns; i know DATA : Text to Columns but won't have anything to Delimit it with and can't use Fixed width as students will study subjects with different length charactors.
I have a table, which has pairs, e.g like this:
A=1
B=2
etc
how to give the value to variable based on this pairs? I mean something like this:
If left(mystring;1)=A, then
myvar=1
I used the case-structure, but maybe there is a better solution, which can use an array in order not to write all of the items whenever I need it.
I've created a useform for recording stock trades. So it asks the user to enter values for
"Date of trade"
"Time of trade"
"Name of stock"
"Price"
Right now I'm using a calendar control I downloaded from
http://www.fontstuff.com/vba/vbatut07.htm and it looks good, but I'd also like to have an easy way for the user to select the time of the trade (in minutes).
Is there an easy way to have a time picker? Like a dropdown box maybe? I'd like to have date and time picked separately since the date will remain the same for a lot of trades but the time will always change.
I've googled it a lot and some people mentioned using the datetimepicker Access add-in but I'm not sure if that's right for me. Also, I'm using Excel 2007 but the form will also be used on excel 2003 machines, will that affect the choice of solution?
I have a CSV file which contains a list of records about offices. This CSV file is generated from another program.
I read the CSV file with VBA, a SELECT * SQL statement which puts the elements into an Office Object and adds the offices to a collection.
For some reason, when one of the Telephone numbers is read it is being identified as a Null value. If I manually open the CSV file and change it to a different number e.g. 12345 and try re-read the CSV file with SQL VBA the number gets read fine. Every other element in the CSV file reads fine except for this. The number itself is 7736786460.
Attached is the small snippets of code to read the CSV file which is fairly straightforward.
Code:
Sub LoadUnits()
Dim oRs As Recordset
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
'Open an ADO connection to the folder specified
Set oConn = CreateObject("ADODB.CONNECTION")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & ";" & _
[code]...