I 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 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 Replies View Relatedway 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 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.
I 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 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 run across a problem that I am unable to solve with a combination of index, match and countif funtions. What I am trying to do is have the formula return disctinct symbols. I have a list of currencies on one sheet: audusd, eurusd, usdcad etc, more than one from each. Id like to return one from each onto another sheet...
View 9 Replies View Relatedis possible to have a drop down list where you can type in the first few letters of a word and have the drop down list go to the the words that begin with those first few letters instead of having to scroll down the entire list to find the word / item you are looking for.
I ask since the drop down list may end up having up to 1000 words in it.
I have 7 cells containing strings but not numbers on a row.
Now I want to list out all the combinations of drawing out 3 cells out of these 7 cells while the remaining cells that haven't been drawn out could also be listed out one column next to the drawn cells.
For example, I got 7 cells like this.
A B C D E F G (each letter in ONE cell)
And I want to list out all the combinations like this:
ABC DEFG
ABD CEFG
ABE CDFG
ABF CDEG
etc.
For more details, please refer to the attached sample (an .xls file being zipped).
How to do a sheet with random letters... I got this macro but is not working
Sub RandomLetters()
Dim RandomRange As Range, cell As Range
Set RandomRange = Range("C10:AG15")
For Each cell In RandomRange
cell.Formula = "=RANDL(A,M)"
Next
RandomRange.Value = RandomRange.Value
End Sub
The range is set. but the cell.formula is not... i want to set the macro to random the letters (N ,M, T, E, DS) without their being repeated at the same column is that possible???
In a sheet (4) I have a pivot refreshed by a VBA macro code. Since I have defined a Chart on this pivot, I need to copy this pivot in a new sheet to avoid to reduce rows using a filter of the Chart. I copy this pivot using this VBA code
Sheet4.Select
Sheet4.Copy After:=Sheet2
I have e new sheet with a number Sheet(n) not equal to maximum sheet number +1
For a new run of the VBA macro I need to delete this sheet
Sheets(n).Select where (n) is not = maximum sheet number +1
ActiveWindow.SelectedSheets.Delete
Then I need to avoid the message box where I need to confirm to delete the sheet because the sheet is not empty.
Is there a solution to copy a pivot in a new sheet replacing the previous pivot and vithout changing the number of the sheet?
I have two sheets in my workbook: One containing names with French and other European characters, the other containing a list of ISO entities for these characters, like these:
#224
#225
#226
#227
#228
#229
#230
I need to replace these characters in Sheet 1 with the equivalent ISO entity from Sheet2 and print the value into the same cell in Sheet1.
I have 2 lists and need replace the numbers in List 1 if they appear in List 2. If they do not match to list 2 then keep the number in List 1. I have attached a excel worksheet as an example. So cell B4 needs to be replaced by Amount in Cell F9. Also Cell B7 needs to be replaced by number in F5.
View 3 Replies View Relatedi 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 words in cell range (i.e. A1:A1000) and I want them to became upper letters.
Excel forum to EXCEL FORUM
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 the following data in sheet 1. In Sheet 2, column A contains a list of data.
What I need to do is to compare data in Column C (sheet 1) to Column A (sheet 2).
If it matches, then Cell value in A will change to be the same value as B.
For example, range C1 of sheet 1 matches that of Range A2 in Sheet 2, then range A1 of Sheet 1 becomes the value of B1.
How can I use VBA to achieve this? Do note the data in Sheet 1 and sheet 2 changes all the time. It can be more or less.
Sheet 1
A
B
C
D
11
12
TH
[Code]..
Sheet 2
A
B
GG
TH
AA
HE
My desired results:
A
B
C
D
12
12
TH
[Code]...
I want to replace the values in column A if a unique value is identified on a particular row. Here's some sample data from Column A
A2= 01/16 Petsmart Inc 1372 Miami FL Card 7777
A3= 02/13 Sir Nicks Brodhead WI Card 7777
A4= 01/05 Canal Center Walk C37436 Orlando Fl Card 7777
A5= 01/24 Shell Oil 5744417710 Atlanta GA Card 7777
A6= 8/12 05 Canal Center Walk C37436 Orlando Fl Card 7777
A7=09/01 05 Canal Center Walk C37436 Orlando Fl Card 7777
A8=12/21 Top Deck Bridges C37436 Denver CO Card 7777
Here's my list in cells D2:D6
Canal Center Walk
PetSmart
Sir Nicks
Top Deck Cafe
I created a list of values in Column D then created the below formula in column B and replicated it down.
=IFERROR(INDEX(D$2:D$6,MATCH(TRUE,SEARCH(D$2:D$6,A2)=1)),A2)
What I would like the formula to do is look at the list in column D. If a particular row shows a match for that text string it will populate that value in column B, but delete everything preceding the text value and after.
Ex: A2 = 01/16 Petsmart Inc 1372 Miami FL Card 7777
B2 should = Petsmart
how can I seperate small and big letters from one collumn, example:
NAME
name
NAME
NAME
name
name
Is there a VBA command to get the letters, instead of the numbers, of the column of a selected cell?
I have to letter a list whihc means setting up a loop using character codes.
I may have to go into double letters so I am working on how I would set up the loop for if and when it gets past 90 and starts on double letters. so far the highest is the letter "U"
of course the easiest would be to pick up a column value as a letter
NT values do not get a number
A_____ _____NT###
B_____1_______C####
C_____2_______RMK###
D____ _______NT####
i am guessing the loop might involve some arithmetic test between the count and the character set 65-90. or maybe a mod thing.
I am working on data that needs to be cleansed of the symbols i.e. *&/- etc so I am hoping that I can automate this as their are over 30,000 rows of data and takes time to go through each find and replace.
View 9 Replies View RelatedIm hoping there is an Excel Formula (not VBA Code) solution to this problem.
I understand how to use the Substitute command in Excel, but I would like to be able to provide a list of words and have the Substitute command use that list to replace every occurrence within a given cell with a blank ("").
The twist to this is, that the List of Words will be Dynamic, and thus the formula will need to account for that.
NOTE: the formula should NOT replace parts of words, e.g., if the List Word is "can" and the cell to be evaluated contains "candle" the formula should NOT replace the "can" in "candle" with "". Only whole word matches should be replaced.
See attached spreadsheet, in sheet 1, yellow columns are for restricted cells and Sheet 2 contains the lists for such columns' validation range. Sub-Strategy is dependent on the choice made from Main-Strategy, e.g. if Main-startegy = EquityHedge, then the choices for sub-strategy can only choose Long Biased, Low Beta & opportunistic. But I also have a "Other" category under Main Strategy, Sheet 2 cell G1, if this is chosen then I would like to remove the data validation in the sub-strategy column, (i.e. u can input anything for sub-strategy) how can I do this?
View 2 Replies View RelatedI found another thread Find And Replace Vba." I have looked and looked but can not find or figure out how, or what, to change to search formulas instead of the calculated value of the cell. I am writing code that will copy 2 sheets to new sheets and then rename the new sheets. Sheet1 and Sheet2 are the original sheets with Sheet2 having formulas that reference cells in Sheet1. I am creating new Sheet3 from Sheet1 and new Sheet4 from Sheet2 and wanto to find and replace all references to Sheet1 in Sheet4 to reference Sheet3 instead.
View 4 Replies View RelatedI create a monthly excel sheet regarding customer & their payment
like this..
Suppose..
Month January 2007
Customer A 2000
Customer B 2000
Cust C 3000
Feb
Cust D 3000
Cust E 4000
Cust A 2000
Mar
Cust B 2000
Cust A 2000
Cust G 4000
Now, I want to do somthing like that If a customers Name repeat in another month, I mean Cust. A name has been already entered in Month January & if it comes again in february then it should be automatically replaced or highlight with red line Or something like that.
Switched to Excel after using OpenOffice and I'm stuck on knowing what an old a 'find and replace' formula would be in Excel. It would remove a specific character (or word) ONLY if it was the last characters in a cell.
The old find and replace for open office:
Find: (.*)/$
Replace: $1
It's not that important now to delete a word, mainly the last slash '/' ONLY if it's the last character e.g. this data has 2 rows with a '/' as the last character
website.com/page
website.com/page/
website.com/page/page/
Running my old find and replace formula would remove the last slashes, but leave the others
website.com/page
website.com/page
website.com/page/page
Need simple replacement to the find and replace but a formula is also right.
Attached is an excel file that contains all possible values in the first column (9238 of them) and a comma separated list of a subset of those values in the second column. I need to find a way to replace the smallest value with 1, the next smallest value with 2, ... , and the largest value with 9238 in every instance. In other words, I need their rank value.
OhioBlockGroup.xlsm
What I a trying to do is I have a sheet with 3 pages and am using excel auto complete to change data in my cells to save typing out every time. for example if I type 22.. in sheet 1 cell a1 and tab away, it replaces it with an address and post code stored in auto complete. is it possible to store my info in another sheet, if it matches the 22.. in say sheet 3 replace it with the text in the next cell to the right
View 5 Replies View Related