I have a column of data which has numbers and units (small example below).
I need to remove the text (units) and at the same time multiply the number by a value which is based on what the text is:
-999.9uA needs to become -999.9*10^-6
-98.40mA needs to become -98.40*10^-3
I have a spreadsheet which reads:
A1 E012345678
A2 126789433
A3 ABCDEFGH
A4 CDEEGFFH
A5 E0456783
A6 98765432
etc.
I need only the the data in Cells A1 A5 etc. which means the 3 cells below (A2, A3, A4 ) should be deleted. A5 I need the data, and then A6, A7, A8 I do not need ... A9 need and so on.
the data should be in in one below the next with no spaces in between.
Data is only in column A.
i have a sheet dealing with part numbers and a paramiter exported from a programme.
L=1000MM | L/R is an example.
now
i use replace to get rid of everything before the number but anything after the mm is totaly different most of the time.
is there a way that i can get rind of anything after, and including, the mm?
or by some chance a command that will just leave me with the number allone?
I have a bunch of cells that have City and Zip Code combinations.
Ex: Chicago 606
Fayettville 72701
SACRAMENTO 95691
Some of them are 3 digit zips and others are 5 digits. I just want to weed out anything with a number leaving the city names.
I have tried to set one formula which will given the the Numbers of the Vehicle. However as there are other numbers also which makes it difficult to do so.
View 5 Replies View RelatedI currently am pasting data that in some cases there are numbers at the end of a list of names e.g Andy Cole10 or James Banks3.
I have used a formula before that seperates the numbers into another cell but now i need only the text and not the number.using A & B column as example and i can adjust the range.
I've put together a macro to format an extract from a fuel report but at the end of it I need to remove numbers in front of drivers names.
E.g.
1. John smith
10. Joe bloggs
123. Peter piper
Should end up as
John smith
Joe blogs
Peter piper
All without the number, full stop and the space before the name.
I have been sent a list of 600 names in the format of 1. john smith 2. jane doe 3. tim smith ect.... how do I remove the numbers without going to each individual cell and manually deleting it?
View 1 Replies View RelatedI have a limited amount of VBA knowledge. My limited knowledge is based on solving my problems by looking at the answers already on this site and trying to adapt it to my needs. Here goes:
I import a list of address and telephone numbers (100+) into excel. example - Cell A1 contains "10 downing street, whitehall, london, SW1A 2AA 01234 567890", sometimes there is another space after the number, sometimes there is no telephone number. I want to remove the telephone number at the end if it is there. Have searched the forums have have come up with the follow solution:
1. import list to column A
2. copy and paste Trim to column B
3. copy values in column B and paste to column A
4. delete column B
5. remove the end numbers
6. copy and paste Trim to column B
7. copy values in column B and paste to column A
8. delete column B
9. remove the end numbers
It looks like this in code:
Sub testa()
'select (=TRIM(A1)) formula and paste to sheet
Sheets("Formulas").Select
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet4").Select
Columns("B:B").Select
ActiveSheet.Paste
[Code]...
'copy values of column B to Column A and delete column B
Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
[Code]...
'copy values of column B to Column A and delete column B
Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
End Sub
I know this is a long winded way around (still chuffed I worked it out by myself though!). If I merge all the macros into one marco it continually loops and deletes everything in the cells and I have to press escape to stop it.
My questions are:
1. Why, when I merge all the macros doesnt it work the same as individual macros? (I would like to know for future reference).
2. Its there a better way to remove the telephone numbers?
I have a huge column of data. This data has few prefixes that I need to remove. I have a list of possible prefixes. Some prefixes are 1,2,3 or 4 characters long. Could you please suggest best way of removing these prefixes (VBA if possible)?
Following are some of the examples of prefixes:
AB
GD
KR
BCD
FP-
TJ-
W
I'm trying to remove all the numbers of a phone number an keep the are code for 2000+ phone numbers.
ex. 111-222-9999
I want to remove the 2's and 9's but keep the 1's.
I am trying to remove a middle initial from a text string however not all of the cells have middle initials. In column B I would like to return the first and last name. If needed I can have the first name in column B and the last in column C then combine them.
Example
John A Smith John Smith
John Smith John Smith
I have a list full of different IDs with different version numbers (This format: AKH123.1).
I'm trying to clean the list - removing all duplicate IDs but leaving only the largest version number.
I managed to remove the last digit using LEN/VLOOKUP and removing duplicates, but leaving only the largest version number is too tricky for me. I've thought about trying something with LARGE but can't wrap my mind around it.
My thought process went like this: I want to find all occurrences of "AKH123" and return the largest one.
The list contains about 8000 entries and this would save en enormous amount of work.
Here is a tiny portion of the list:
AKH450.1
AKH451.1
AKH451.3
AKH451.4
AKH451.5
AKH451.6
AKH451.7
AKH451.8
AKH452.1
AKH453.3
AKH454.1
AKH455.1
AKH455.2
I have a spreadsheet with Mobile phone numbers in the following format:
+44(0)77 7296 5210
The spreadsheet has 2500 of these phone numbers.
Is there a way to remove the brackets and the +44 to leave:
07772965210
I am using Microsoft Office for Mac 2011
i would like to take the first letter of a cell and add 1000 to it.
At the minute, ive only been able to take away the first letter which isnt what i want to do
so far i had
=RIGHT(B1,LEN(B1)-1)
I have 2 columns of data which contains mainly numbers that are either 9 or 10 digits long. However some have got so words at he end which is what I want to remove. I thought of using text to colums but there is no space or symbol between the number and text.
An example is
0111111100ddddfffffddd
or
0222222222ddddd ffffff ddddd
An externally generated CSV file gives us one column which shows a location, year and then individual names in brackets, like so:
London 2013 (Firstname Lastname)
Delhi 2012 (Firstname Lastname)
I'm wondering if it is possible to separate out the text before the parentheses, so that I can show "London 2012" (for example) in another cell. The number of characters changes from row to row, so using the LEFT and RIGHT functions doesn't quite do the job.
Attached is a sample sheet with nine columns. Column D includes a name, but in many cases it is preceeded by: 'TO', 'BY', or 'OF' and a space which I need to remove (if they exist), leaving just the name. Please note that sometimes just the name exists so nothing needs to be done. I believe that in a jet sql querie I can use something like:
View 7 Replies View RelatedI have imported data and it shows "Check 1234". How can I use a formula to remove "check" from this cell?
View 3 Replies View RelatedI belong to a fantasy cycling league and after every race, I copy the race results to award each rider their points for the day. The site I visit most frequently lists the riders results in this format:
1. Alberto Contador (Spa) Team Sax Bank Tinkoff 4:05:00
2. Lance Armstrong (USA) Team Livestrong etc... 4:06:00
I usually copy the results to my spreadsheet then calculate each riders points.
The'results end up looking like this in Excel:
1. Alberto Contador (Spa) Team Sax Bank Tinkoff 4:05:00
2. Lance Armstrong (USA) Team Livestrong 4:06:00
So, the data ends up in 3 cells. The position in one cell, the rider and their nationality and their team in another, and the time of arrival in another cell
Is there a way in Excel where I can get rid of or delete all the data after a rider's name?
I usually end up pasting the rider information to Word, then replacing the (***) with a tab symbol, then pasting back to Excel and get rid of the data that's after the name by deleting the cells to which that data was transferred.
Is there an easier way to do this in Excel, or a macro I can create?...
E122112 David Hall
Robert Townsend
Micheal Keel
Tanya Smith
Elizabeth Charles
E004587 Andrea Tummings
The problem is for those names that come thorugh with the Letter and than the number...I don't need the names like that....I would like a Macro that would look at each cell in column A and remove that from those selected cells. I would like my result to look like this...
David Hall
Robert Townsend
Micheal Keel
Tanya Smith
Elizabeth Charles
Andrea Tummings
I have about 10,000 part numbers all with descriptions. These descriptions do not follow a uniform logic;
=============
Part 1: 5x10 Red Cotton candy
Part 2: Yellow 6/18 x7 TTC x11 Picture Frame
=============
My dilemma is that I need to figure out a way to remove everything aside from the "5x10" and "6/18 x7 TTC x11" from these descriptions. I just need the the item sizing.
What I've come up with so far is sorting items by description and working with batches of similar descriptions, then doing replace "Red*" with "blank". It works but it still takes me too much time and it's not perfect.
My question is there something else I can do that is easier and more accurate?
We often get spreadsheets from our customer that are formatted with both comma and dash combinations. We would like to only have the comma seperation.
Example:
R1, R2, R3-R5, R30
Result:
R1, R2, R3, R4, R5, R30
There is usually only one alpha character but sometimes more. Example: CR1, CR2, CR3-CR5.
The following macro works great if there are no alpha characters. how to solve the alpha/numeric combination?
Function Nums(rng As Range) As String
Dim adnum As Integer, n As Integer, num, Txt As String
num = Split(rng, ",")
[Code]....
Any formula for removing text within multiple sets of parentheses?
I would like to take something like this: Compared with placebo, dimethyl fumarate was shown to be effective in the treatment of patients with MS in the phase 3 DEFINE (Gold R et al. N Engl J Med. 2012; 367:1098-1107) and CONFIRM (Fox R et al. N Engl J Med. 2012;367:1087-1097) trials. Common adverse effects associated with dimethyl fumarate that were observed in these trials included flushing and GI events, as well as decreased lymphocyte counts and elevated liver aminotransferase levels.
and have it read: Compared with placebo, dimethyl fumarate was shown to be effective in the treatment of patients with MS in the phase 3 DEFINE and CONFIRM trials. Common adverse effects associated with dimethyl fumarate that were observed in these trials included flushing and GI events, as well as decreased lymphocyte counts and elevated liver aminotransferase levels.
I wish to automate this task so formulas (if possible) are preferred. Totally cool with a multi-formula, many-columned solution - as long as this task is automated.
NB: Each cell will contain different text, so the sets of parentheses will appear in different places. The number of parenthetical sets may also vary from 1-5.
I am trying to write a VBA application within Excel to go through all Lotus Notes e-mails in a specific directory and save the attachment to a specific directory, remove the attachment and insert text into the e-mail in the spot the attachment was located that states "The attachment has been removed".
I'd also like to insert a 1KB text file into the e-mail that would be called "File Removed.txt" that would still allow you to quickly find the e-mails that had attachments because the little paperclip would show up.
Some code below which I have inherited, basically its looping through one column of data and removing the text at the beginning before the hyphen eg. '123 - data text' to 'data text'.
HTML Code:
'Remove everything before the hyphen in the activity column
Dim SearchStr
Dim CharOffset As Variant
Range("c7").Select
[Code] ......
I have text of size 14 and 18 mixed in cells in a column. Cells are font size 18 or mixed with both 14 and 18 size text. I need to sort out the text with one column of size 14 and another of 18 only. I am thinking of copying and pasting the column twice and run a macro in first column to remove the text of size 14, and another macro to delete text of size 18 in second column. I need the leftover text to be in same rows.
I tried everywhere and couldn't find a macro for mixed text cells. I am using Microsoft Excel 2010.
I am extracting data from a website to excel 2010. my problem is the data contains both text, numbers, and sometimes a combination of both.
e.g. hi im ron for more information you can reach me at 6 five 6 four 5 seven 7 three 2 two..
I need it to look like this 6564577322 or 7 * 0 * 2 * 4 * 5 * 6 * 8 * 6 * 2 * 1...i need to look look the same
How do i first format the numbers written in text into numbers and then show only the numbers in a cell minus the text?
I have a text file with rows and columns of numbers ranging from 1-4 digits that I'd like to import/copy into Excel with each number being in its own cell. But whenever I copy/try to import, Excel splits all of the 3-4 digit numbers up into single digit numbers. The text file has 10,000+ columns (each number occupies two columns so I have half of that amount in numbers) and 300+ lines.
Is the file simply too large for Excel to handle or is there a way I can do this?
In the ID column of the attached excel file whenever I convert the numbers stored as text to numbers it results in a weird transformation.
e.g. an ID of 480610141001 becomes 4.8061E+11. When I do the same process in the name column, which has similar numbers, it will give the correct result following the same process.