Free Microsoft Excel 2013 Quick Reference

Copy certain values (9 digits, numbers only) from range

Right, I'm not sure where to start. It is probably good idea to check the attached file. I have a spreadsheet where I would like to extract some data. Namely I'm after numeric values that are exactly 9 digits. The information is spread across the sheet and I would like to get a list on numbers in Column A on sheet called "output". What makes it harder is that the numbers are within merged cells and there are rows of irrelevant data between the numbers. Positive side is that there is system or pattern.
The data should be extracted from the columns below (last row as per last used cell of column C):
Or just maybe the whole area AH9:IE(last row used) can be looked at in one go, and only the 9 digit numbers to be copied over.

The data should go to sheet "Output" and should be in A1:A (no header). Example sheet Output (After) is included.

if anyone feels up for the challenge and would like to help me out, I would be very thankful. I am getting better at things and I have created some wonderful macros at my work place but there is still so many things that I don't know how to do.

Cheers


Hello friends,

I need a macro which would copy only 9 digit numbers from txt file to excel. Thanks in advance if this is possible :D

hi everybody,

There is a need to validate a cell to insert only from 9 digit number to 13
digit number.

For Example: 000000001 to 9999999999999. ( I want to ristrict the
user to enter even 8 digit number i.e. 99999999)

Thanks.

Hi,
I'm trying to find a better (smarter) way of extracting a 9 digit number from a string.
The number can start anywhere within the string, and the string may contain other numbers (though not immediately adjacent to the 9 digit number).

I wrote a clumsy macro to text each character (from left to right) to see if it is a number (using a 'if variable >=chr(48) and variable <=chr(57) test) and then tested each of the following 8 characters. Once I established that I had a group of 9 numbers in the string then put the 9 numbers into an adjacent cell.
Then looped through the column with the strings.

I'm thinking there is a better way to do this and any suggestions / pointers most appreciated.

I am attaching a macro which would extact all 9 digit numbers from a txt file. But the challenge is I get some numbers such as ROTF01361 which is indeed 9 digit but also an alpha numeric number! Can anyone edit my macro and give it to me so that the macro identifies and extracts such numbers too

Thank you

Hi there,
I am applying for a job and they gave me a rather complicated excel quiz and I am stuck... they want me to extract the first three numbers only from a list of phone numbers in a new column... Seems simple, just use the mid function. However, each phone number is differently entered such as some have parenthesis in front of the first three numbers some don't etc... What function can I use to achieve this?

It looks like this and I need to extract the first three numbers i.e. area code from each in a new column... how do I do this while eliminating unwanted characters from certain numbers that have parenthesis in front of it?

Client Phone Numbers
(817) 424-6099
624-863-2945
8546826059 ext.682

Any help would be appreciated. Thanks so much!

Hello....I'd appreciate some assistance (again). I have a 9-digit number that represents the GMT day and time (from the beginning of the year) that I'd like converted into the local day and time. So, the number, for example is 076003449 in cell A1. The first 3 digits represent how many days have passed from Jan 1 (076 = March 15, Jan 1 = 1 and this year was a leap year). The remaining digits represent GMT time (003449 = 12:34 AM GMT; I don't care about the seconds). And, I'd like the GMT time converted to Pacific time (12:34 AM = 4:34 PM Pacific the previous day - so, the local date now becomes 14 March). What I'd like to appear in cell B1, then, is 3/14/12 4:34 PM. The year is the current year; i.e., 2012. I can adjust for the new year. Many, many thanks in advance for your help!!!

Hi All,
I have a data set that I imported from Access. One of the columns contains the code for specific work activities, for example 13Z or 9A. I need to extract the numbers only from the cells in that column so that they are in separate cells in a separate column. I've been trying to use left, right, or mid functions, as well as text to columns with varying degrees of success. Any ideas on what the best method for doing this may be?

Thanks in advance!

Hi All,

I have been given the below bit of VBA from a friend to extract the numbers only from a cell (which works fine) but it extracts all the numbers and puts them all together without and spaces or delimiter which is a bit of a problem as i need to put each number that i extract into seperate cells (I was planning on doing this with text to coulms once i had them extracted)

Function GetNumbers(cText As String)
Dim Lenc As Integer
Dim x As Integer
Lenc = Len(cText)
x = 1
Do While x < Lenc
If IsNumeric(Mid(cText, x, 1)) Then
GetNumbers = GetNumbers & Mid(cText, x, 1)
End If
x = x + 1
Loop
End Function
Can any one help with this?

Many thanks

Jamie

Hi,
I've a large excel file contain a sheet of values. With a Macro I would like to
1) find the smallest absolute value in a certain column
2) find the row number where this value was found
3) Copy certain values from this row to another place.

Looking forward to your reply

Dear All

I am trying to create a macro to copy certain values from one sheet to another sheet in specific location. Please find the attached sheet for reference
Steps for automation: Please open the attached document to understand better.
1) In the attached document from LPS sheet I need to copy the headers (Range C:S) to another sheet named “performance test”. In performance test I need to paste those values in the range (A7:A14) under transaction name.
2) Then I need to copy specific 90% values Response time (Range: E97, G97, I97, K97, M97, O97, Q97, S97) to performance test sheet (Range: L7 to L14). We need to copy only the values of E97:S97 to L7:L14 not the formula. The going to be copied values is not in continuous range.
3) Finally in the Performance test sheet the copied value (L7:L14) will be compared to the previous column (k7:k14) and if the difference is positive then the value in the copied cell should change the color to amber.
4) In the document in sheet1, I just want to give input for copy target cells and paste target cells so that the copied values can be pasted wherever I want. Also the target compared value cell in sheet1 as input.

Your help will be much appreciated.

Thanks
Muthu

Hi,

I want to copy certain values (contained in a row) from "Sheet 1" and paste them to their respective places in "Sheet 2" (within the row) IF a certain entry (in a column) is the same between both worksheets.

example:

(Sheet 1)
ref---PA#---Data1---Data2---Data3
sa23----002-----12------Ball-----Cat
ds2-----05-------13-----true----ref235
ds22----02_1234--23.5--e mail----jack

(Sheet 2)
A---------B--------C------D---------E--------F-------G--------H
01
002---------------(Data2)-----------(Data1)----------(Data3)
16
05----------------(Data2)-----------(Data1)----------(Data3)
03_1234
02_1234----------(Data2)-----------(Data2)----------(Data3)

So in this example the values contained in the rows in SHEET 1 were copied to their respective places in SHEET 2 depending on the matching values between Column PA# (Sheet 1) and Column A (Sheet 2).

Please let me know how to do this through Macro Code (VBA) or any other Excel method.

Thank You,
JN

How can I create a conditional formula that would interpret the day of the month to either calculate a formula or not, based on that day's numerical value within its month(1-31).
For instance: (SEE SAMPLEDATA.XLS)
IF([E9]

Hi All,
It would speed a process up at work if I could copy & paste a 20 digit number, but when I try to paste, I get 1.23457E+17. What am I doing wrong please ??
Thanks,
papillon

So I tried to get this figured out earlier today, and even had some help from Blake over in the general forums but to no avail we're still stuck.

http://www.excelforum.com/excel-gene...rt-filter.html

^ original thread if you want to reference it.

I have an idea on the logic behind the code, but I'm not sure on the syntax to write it.

Basically the module would do a find function which would locate "100", using something similar to Ctrl+F's Match Entire Cell Contents ability. Upon finding the value, it would copy the value(100), the number in the cell directly to the left of it (which is the scores corresponding time), and paste those into a new sheet with the same header they were under in the previous sheet. After finding the first value of 100 in a column, the module would begin to process the next-next column over (every-other one). If a score column didn't contain 100, it simply gets skipped over.

I know there's going to be some IF logic in there but it's been about 4 years since I've coded so I know it'll be rough trying to write out the syntax for it.

In short I need to sort every-other column (the score columns) to figure out in which cases a participant hit 100, and if they did I need the time which they hit 100.

I've tried using a table and pivot table to do this but in both cases it's just pointing and clicking through the various values and with a master document of about 12000x400 its taking dreadfully long.

I've attached a a document with a small portion of the data. If any of the VB guru's out there can help you guy's would be life savers.

I need to copy cell values down a certain quantity of times automatically. For example, I would like to copy the following 4 times:

REL123
REL259
REL859
REL897

I need it to appear like this:

REL123
REL123
REL123
REL123
REL259
REL259
REL259
REL259
REL859
REL859
REL859
REL859
REL897
REL897
REL897
REL897

Sometimes it might be 4 times or sometimes 6 times.

I just need to duplicate the cells down, not the entire row.

Any ideas?

guys,

values is 012345678910111517
it returns either 01234567891012100000 or 1.23457E+17

i searched a lot on google, regarding this issues
but i doesnt got proper solution

here is the solution i got
1. add apostohe to the the number and convert to string ' 012345678910111517
2. convert the cell to number but it will return 1234567891012100000,elminating 0 at first postion

but i want plain full 15 o 20 digits number only

Hi,

I want to copy certain values (contained in a row) from "Sheet 1" and paste them to their respective places in "Sheet 2" (within the row) IF a certain entry (in a column) is the same between both worksheets.

example:

(Sheet 1)
ref---PA#---Data1---Data2---Data3
sa23----002-----12------Ball-----Cat
ds2-----05-------13-----true----ref235
ds22----02_1234--23.5--e mail----jack

(Sheet 2)
A---------B--------C------D---------E--------F-------G--------H
01
002---------------(Data2)-----------(Data1)----------(Data3)
16
05----------------(Data2)-----------(Data1)----------(Data3)
03_1234
02_1234----------(Data2)-----------(Data2)----------(Data3)

So in this example the values contained in the rows in SHEET 1 were copied to their respective places in SHEET 2 depending on the matching values between Column PA# (Sheet 1) and Column A (Sheet 2).

Please let me know how to do this through Macro Code (VBA) or any other Excel method.

Thank You,
JN

Duplicate Posting can be found here:
http://www.mrexcel.com/forum/showthread.php?t=504357

Hi, guys! I news help on this one. I would like to create a macro to copy sheet, name it based on cells from another sheet. I managed, through research, to execute it through looping. see code below:

Sub copySheet2()
Dim rngName As Range
Dim i As Integer
Set rngName = ThisWorkbook.Sheets("Sheet1").Range("a1")
Do Until rngName.Value = ""
i = ThisWorkbook.Sheets.Count
Sheets("Sheet2").Copy After:=Sheets(i)
ThisWorkbook.Sheets(i + 1).Name = rngName.Value
Set rngName = rngName.Offset(1)
Loop
End Sub
It worked well, however, I also need to link or copy values corresponding to each name before it stops executing e.g. Name, Position from Sheet1. I would appreciate any help. Thanks!

Hi,
Can you guide how to extract seven digit number set from one cell.... for example,
I have data like this
A1 : 2267854, 2290938
A2: 2267854, 2290938, 2289097
A3: 2289450, 2747549, 9948546748
How to extract first set of seven digit numbers in one cell and second set in another cell
Thanks in advance
Regards,
Nicks

I need to convert a standard dollar amount (eg 157.62) to a 9-digit
number without decimals or rounding (eg 000015762). The formulas I've
tried so far a

For Each rw In ActiveSheet.UsedRange.Rows
If Application.CountIf(rw, "*monetary*") > 0 Then
With rw
.NumberFormat = "0000000.00;(0000000.00)"
End With
End If
Next

Which formats as "0000157.62". And:

For Each rw In ActiveSheet.UsedRange.Rows
If Application.CountIf(rw, "*monetary*") > 0 Then
With rw
.NumberFormat = "000000000;(000000000)"
End With
End If
Next

Which formats as "000000158".

Does such a thing exist? Any assistance would be greatly appreciated.
Thanks in advance.

Is there a formula that will pull the numbers only from
the letters and numbers mixed in?
Example: D16M644HGUD

Thank you!

Hi,

I am trying to copy a value of cells to another worksheet. For example, I want to copy the value of B28 and F28 from the different sheets into the summary worksheet. I know this can be done by using "='sheet name'!B28 " but I hope it can be done using macro as I will eventually add more sheets. I've attached sample spreadsheet.

Any help will be greatly appreciated.

At work we continually copy and paste 16 digit numbers but I have not been able to figure out why excel inputs an extra 0 at the end of the 16 digit number. We have to use excel to speed up our work. Can someone help please.

I need to convert a standard dollar amount (eg 157.62) to a 9-digit
number without decimals or rounding (eg 000015762). The formulas I've
tried so far are:

For Each rw In ActiveSheet.UsedRange.Rows
If Application.CountIf(rw, "*monetary*") > 0 Then
With rw
.NumberFormat = "0000000.00;(0000000.00)"
End With
End If
Next

Which formats as "0000157.62". And:

For Each rw In ActiveSheet.UsedRange.Rows
If Application.CountIf(rw, "*monetary*") > 0 Then
With rw
.NumberFormat = "000000000;(000000000)"
End With
End If
Next

Which formats as "000000158".

Does such a thing exist? Any assistance would be greatly appreciated.
Thanks in advance.