I would like to easily replace every character in column A with a blank, with the exception of - and a number. I am trying to get rid of all letters,:, / and other miscellaneous characters without having to delete them one character at a time.
View 7 RepliesI have to convert an old dos program file to excel. I already have the list in excel but the text is in strange symbols. I know which symbol represents which
letter. As have many files to convert each time I need a makro which would save me lot of time. I have two worksheets: "1" and "2". I need a makro which replaces symbols in sheet "1" Column A through key list in sheet "2".
Example:...............
I have a column of invoice numbers, formatted as text, some of which have first character "0" & last character "C" e.g. "012345C". I want to get rid of the "C" leaving e.g. "012345".
I have tried Find "C" / Replace " " with the undesirable result being that the leading "0" disappears too. Find "5C" replace "5" gives the same.
way to convert all symbols ( ) to their equvilant letters (u a) I know that I can do this manualuy with Find replace but I was hoping there was some magic that I am missing.
Please note that the exapples are only 2 of the symbols I need to convert the sheet could contain all of the varieties of symbol.
I have two worksheets: "1" and "2" Now i want to filter all symbols used in Column A sheet "1" and list them in Column A of sheet "2". The results should contain no duplicates. For example:..........
View 5 Replies View RelatedI am looking for a syntax or function wherin i can replace all lines in different cells containing specific string or text in it. e.g. Few cells in sheet contains " This guy is for deployment", other few cells may contain "guy is for core deployment" and other cells may have " project is specific deployment". I want to replace all such lines which contain a word "deployment" with ony one word "Deployment".
View 7 Replies View RelatedI am importing content control information in a word file into excel by using a macro in excel. The information is coming across nicely but when the information about a checkbox comes across, in excel, that's what you see, a checkbox (☐ or ☒). Is there any way (formula or vba) to replace all the checkboxes with a yes or a no?
I wrote a formula that works:
Code:
=IF(G8="☒","YeS","nO")
But this will add this information to a new column and I would like to replace the information in the existing column (the form has about 200 checkboxes) and I don't want to add a bunch of new columns.
I also toyed with VBA:
Code:
Range("G1:G100").Replace "&", "No"
But when I try to paste the ☐ in the vba code, I get a "?" so I am assuming it doesn't recognize the ☐ as a valid character.
So I was given the task to translate some procedure instructions in an Excel file from English to Spanish. These are very simple instructions and in most cases repetitive throughout the document. There are a lot of instances where the instructions are the same except for a #. They are meant to be modified within the cell as the text that appears in the cell will be printed exactly as it appears.There are several instructions in the cell, it is basically a long continuous string in the cell. My idea is to create a macro that can search that range of cells for the instruction, replace it with the Spanish equivalent while leaving the original English instruction in the cell.
View 6 Replies View RelatedI have a find and replace function that removes + smybols from a coloum of strings. How can I remove the first instance of a space (if it later contains a +, too?
E.g.
Before: [ +Test +Test]
After: [+Test +Test]
Code:
Columns("D:D").Select
Selection.Replace What:="+", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
i have a list of 2000 fields which have the same format IE "AB10014"
I need to remove the "AB" from every field and leave the #.
Besides putting a space and running text to columns I'm not sure how.
I have a column of cells, some blank, some containing just numbers, some containing just letters, some containing numbers preceded by the the letter 'p'
E.g.
frt
34.2
36
p34.5
In the cells containing the number preceded by the 'p' - i would like to remove the 'p' leaving just the number, with all other cells remaining unchanged.
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?
Macro that will strip out all the numbers in 1 particular cell column and and leave the text.
View 9 Replies View Relatedi m creating a macro to delete extra characters in a column. I have over 200,000.00 records in one column which consists of numbers with characters. For example:
#80723666
-80726960
80730187--
/ 80730279
80736277 /
( )80739210
* 80739823
& 80735380
80796440 @
## 80722138
if anybody can come up with a macro, so that I can just have the numbers and nothing else. I would like to exclude the following -~!@#$%^&*()_=+?/.";:|][{},^` within a cell.
Can i find a list anywhere of the new Excel 2007 ALT numbers to give the symbols eg Alt0186 = the degrees symbol in 03 but doesnt work in 07?
View 10 Replies View RelatedHere is the formula I am using however it interprets the blank cells as 0% and includes it in the average. To make it more difficult there will be some 0%'s.
View 4 Replies View RelatedI need to remove only numbers from excel cell - I have tried using the "constant" function but did not work. I have thousands of cells with a consistent 4 digit number like this:
1000 the rest is text
How can I remove only numbers but leave all text in the cell and then if I can trim the cell so there are not any spaces at the beginning afte removing the numbers.
i have colums of data containing random figures containing letters and numbers.
I want to completely remove the letters from the colums, leaving me with just a numerical figure. Is there a way to do this? There is no set pattern to work with as there could be 1 cell with 2 numbers and 6 letters, and the next cell may have 6 numbers and 1 letter.
EG: COLUMN A
aaa567783a
3782dc
23388fjjas2
22dd
I want this to simply be
EG: COLUMN A
567783
3782
233882
22
Lotus 123 had a function where you could graphically, in a cell, show plus symbols(+) or negative symbols (-) for the value of the data. For example if you had a column of numbers that you calculated from the previous year that indicate the change, Lotus, in the next cell would allow you to graphically display the symbols that indicate the strength of the change. So if your result was a positive ten then ten plus symbols would be shown. If it was -20 then 20 negative symbols would be shown. Now the numbers I am looking at could be 600,00 and I believe there was a way to to proportion the symbols for example for every 100,000 put in one symbol so the result would be 6 (+) signs.
View 5 Replies View RelatedI have a linked document, only 2 sheets. On the first sheet there is a column for + or - signs, I need a formula for the second sheet to show the + as a 6 and the - as a 5 instead of just the symbols carrying over.
View 3 Replies View RelatedI am looking to remove numbers and symbols from a single cell.
For example, I start with something like:
Cell A1: M. Flank1, T.D. Chang1*, T. Person2, J. Doe3
Cell A2: T.R. Money1*, Y.R. You2
...
and I want:
Cell B1: M. Flank, T.D. Chang, T. Person, J. Doe
Cell B2: T.R. Money, Y.R. You
...
And, there are about 300 more, with varying number of people per cell.
I'm not great with Macros, so if there is a way without macros, that would be good, but, if a macro works best, I can give it a shot..
I have an Excel workbook with around 100 worksheets. All of them have similar columns, but the values are stored as text. The length of columns and missingness is different in different worksheets. I will be grateful if any of you could suggest a VBA that could convert the numbers stored as text back to numbers. I have been looking for an answer for a while, but cant seem to find one that automatically looks at every worksheet and converts text into number.
View 1 Replies View RelatedUsing the following data
R10-12128
R11-12x12x8
R11-12x12x8
R1-12x12x8
R1-12x12x8
R12-12x12x8
R14-12x12x8
R16-12x12x8
R18-12x12x8
R2-12x12x8
I want it to sort like this:
R1-12x12x8
R1-12x12x8
R2-12x12x8
R10-12128
R11-12x12x8
R11-12x12x8
R12-12x12x8
R14-12x12x8
R16-12x12x8
R18-12x12x8
What is the formula to achieve this?
I have a spreadsheet wtih a number of rows that contain answers to different criertia in each column.
The row may therefore have cells that either have numbers or letters.
Column A will be the title of the row.
I want to write a function to find the average value of the numbers in any row with title 'x'.
So it would look down column A, and look for those called 'x', and then average all the numbers across all those rows.
I have tried to use =averageif, but I think the fact there are letters in the cells being assessed (which I just want to be ignored) creates an error.
Tallying entires from a PDF into excel. Such that
c=1000
d=15000
e=50000
f=100000
g=250000
h=500000
i=1000000
j=5000000
k=2500000
l=50000000
In a cell I would like to type =c+d+c for example and have it say 17,000 rather then me having to type in =1000+15000+1000.
I created this data worksheet with many tabs and multiple users. On the main tab, something weird has happened and I have no clue how it happened or how to fix it....
View 5 Replies View RelatedThis formula will add up the letters D,N,H with one value
Like =SUM(COUNTIF(B4:AZ4,{"D","N","H"}))*12 will =36
How can I add bN =17 bmN =19 bD=24
=SUM(COUNTIF(B4:AZ4,{"bN","bmN","bD"})) to add up to 60
I'm working on a function that will sum a row of cells containing letters and numbers. I am currently using :
=SUMPRODUCT((0&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5:S5,"T",""),"TP",""),"TPE",""),"P",""))+0) as my base.
Right now the formula will work with T, TP, and P; however as soon as I type TPE I get #value.
Either way you get the point of what I am trying to do, however my cells can contain any one of the following combinations:
T,TP,TPE
P,PE,
E
In column A I have the following numbers
13710
14782H
9827
14782
14206B
a formula that looks at this range of cells and returns only the cells that have a letter at the end. The letter range goes A to M
I am a school teacher trying to adjust my tracking sheet to calculate pupils levels. I am looking for 2 potential formulas that will do the following.
1 - In cell AE I would like to generate a formula that will take the data entered in cells J:5, L:5, N:5, P:5, R:5, T:5, V:5, X:5, Z:5, AB:5 and AD:5 and give an average level.
2 - In cell AH is it possible to generate a formula that will calculate how many levels of progress the pupils are making - In other words I need Cell I to be calculated against cell J to see how much progress the pupils are making - for example if in cell I:5, a pupil is was given a 3a, and then in cell J:5 is given a 4b, they will have made 2 sub levels of progress. As well as this, can that progress then be averaged out across cells I:5, K:5, L:5, M:5, O:5, Q:5, S:5, U:5, W:5, Y:5, AA:5 and AC:5 to give an overall number of of levels of progress? An then..... can I colour co-ordinate the cell so that if the pupils are making 3 or more sub levels of progress it turns green, 2 sub levels orange and 1 sub level red?
Levels work like this
3c
3b
3a
4c
4b
4a
5c
5b
5a and so on