I need help with a formula (no VBA) that will find all the semi-colons ";" and pound signs "#" in a cell and replace them with this "<br> - ".
Example of Cell A1: ;#Red;#Green;#Blue;#
I started with this:
=REPLACE(MultChoice,SEARCH(";#",MultChoice),1,"<br> - ")
but it only replaces the first character.
I recently became owner of a spreadsheet with some issues, and I am trying to make it useful. Each row has a URL of a blog post, and I want to extract the date from it (which is present in each URL) while getting rid of the rest of the URL. I was able to get rid of everything up to the year (which comes first), but then the URL continues, for example, 2013/05/16/the-rest-of-the-url/ and I would like to just have 2013/05/16 remain.
I am trying to use find and replace with the find box reading 2013/??/??/*/ and replacing it with 2013/??/?? which effectively erases everything else in the url, but leaves ?? instead of the numbers. Is there any way to have it so that it keeps whatever was in the original box?
I have some records that include greek addresses. The greek alphabet contains letters that are common to the english one (A,B,E,Z,H,I,K,M,N,O,P,T,Y,X) and records with mixed characters will cause me problem. What can I do to find and replace any of those letters with the greek version of the letter.
example:
for a record:
T.È.5123 (this is the Greek abbreviation for P.O.BOX 5123)
I'm looking for a function to replace the first letter T (wether is Greek or English) with the Greek version of it
example 2:
KYMHÓ 6 (kimis is the street, 6 the number)
I should replace English (first four ) letters with the Greek version...
example 3:
BAËÁÙPITOY 14 (valaoritou is the street, 14 the number) ...........
I download several sets of data from an internet site. The only way I can do this is to copy and paste into Excel. The problem is, that all of the cells with numbers in them show up as text, and when I press F2, I find that there is a blank space at the end of each number.
Find and replace, searching for a blank space does not work - Excel "cannot find any data to replace". That function does work on cells in which I have created my own space at the end of a number, but not on these numbers. That leads me to believe that that "blank space" is actually some other invisible character that I need to be searching for. I've attached a bit of my data - maybe somebody else can identify my phantom character.
To get around this, I have been using the formula =(left(A1,len(A1)-1), and that works, it's just that it is a pain to create another table of 20-some columns with this formula, then copy those to values, then for each column convert text to columns to change the text to values, then search and replace for #value! where the original cells were blank.
I basically want to find and replace certain characters in a formula, but the formula is quite intricate so I will give some background to the problem first:
I have a complex spread sheet which is used to check if a data array matches another data array (both data arrays are different sizes). One array has a set of parameters for a variable and the other array has the variables and the actual values for each variable. I have a section with the following formulas to check if the actual value (C121&D121) matches the parameters ($A$9:$A$108, $B$9:$B$108) & ($C$9:$C$108, $D$9:$D$108)
I have the following formula in cell AB121:
=(C121=INDEX($B$9:$B$108,ROW(101:101)-INT(ROW(100:100)/$D$1)*$D$1))
In cell AC121:
=(D121=INDEX($D$9:$D$108,ROW(101:101)-INT(ROW(100:100)/$D$1)*$D$1))
And have similar formulas from AD121 to AY121. I mention this to point out that excel regards this as an inconsistent formula, I haven't copied and pasted it over, I have had to re-enter it in each cell adjusting the parameter sections.
The problem I have is that currently this is set up to check through a data set and wrap around every 100 rows ('100' Entered in $D$1) but I want to adjust the formula when there is a different amount of rows to check. So basically the only way I can think to be able to do this is to have a formula or something in VBA (which I have very low level experience with) to be able to go into the formula and change certain characters within the formula. For example, if I wanted to check 95 rows instead of 100, it would need to have 95 in $D$1, which I enter in manually, but then how to go in and replace (101:101)-INT(ROW(100:100) with (96:96)-INT(ROW(95:95) in 2 sections in the 1 formula then do the same with the inconsistent formula from AC121:AY121?
I have about 40,000 lines in Column A that have text like so...
13HMPS32TRE600001OP000000601
13L9KUPSOTE600001OP000000601
I need a formula to change all TR and OT to characters QT. I did a find and replace but its changing other columns of text that I don't need changed.
I have list of email ids and some of which contains accented characters.I need to replace these accented characters with regular character.
Example:
rian.pieda@intt.com.cn
ario.sancez@acca.com
i'm looking for macro for find and replace characters in a locked sheet, if a cell contains for example Monday (First day of week)
I want with a command or macro to change the (First day of week) in (Top of the Week) like this Monday (Top of the Week)
QUESTION: How do I check to see if a cell contains either "" or "/" and if it does then replace it with "-"? THE REASON: I have a form which needs to be saved in a particular directory with the name "Form 1 - client name - matter name". To do this I have put =("G:UsersCComplianceFile OpeningForm 1 - "&B7&" - "&D42) in Cell A1 and then in VBA added the following macro to a button which saves the file
ThisFile = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
This works ok BUT, if the user puts "/" or "" in either B7 or D42, it won't save and throws up an error because you can't use those signs when saving a file.
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 am trying to create a macro where it finds a a certain word in a column for example C. What i want it to do is find anything that says FWD_EUR and then replace that cell (e.g C2) with CASH_EUR_FWD and after it has done that it replaces the adjacent cell (e.g. D2) with EUR_FWD. I then want this to do the same with FWD_USD to CASH_USD_FWD and adjacent cell to USD_FWD.
View 2 Replies View RelatedThe background to my problem is that I'm trying to create a unique dictionary for a game I'm programming. I have imported the dictionary into Excel and need to manipulate it with the following conditions:
Word length can only be 3-5 characters longWord cannot contain special characters like hyphen or apostropheWord cannot have repeated characters
I got the first two criteria done using pretty brute for methods. The first using the LEN function to find words greater than 2 and those less than 6 and then comparing those two lists to get the list of 3-5 character words. The second I did manual search for characters and replaced them with six random characters so they would fail criteria 1 and be filtered out.
The last one I'm having problems figuring out. The game I'm programming only lets you use a letter once. So words like "dad" (or coincidentally enough "mom") would not be valid.
Is there a way I can look at the words in the cell and see if any character is repeated and then act upon that (e.g., copy to another column if no letters are repeated)?
I have a file that I import into excel and append to the end of the last free position, However my totals are slightly out when compared to the main system.
I have found the reason why, they randomly slips in a Credit or credits.
So I have the scenario column P has a txt field and the majority of the time it has SI (Invoice) however when they issue a credit the txt becomes SC.
The annoying bit is that Column L has a value but it is not a negative number i.e. 75 where it should be -75
I can do an iF then and find the SC or SC's and I can place some text in Column L but I would like to be able to take the value in L and make it a negative number.
This problem has come up fairly frequently lately, and I'm not sure how to fix it, or if this is by design...but in Excel 2003 I can't seem to do a "Find and Replace" based on the value of a cell. I can do a find, based on cell value, but the moment I change to the replace tab, the "values" and "comments" are missing from the "look in" dropdown.
I've only noticed this when I'm trying to replace on a filtered list, so I'm not sure if that is part of the issue.
Perhaps an alternative way of arriving at the same goal. Basically I have a worksheet with a number of filtered columns. They are filtered just right, using custom filtering, and so I do not want to undo the filters. In some columns I have formulas that are returning #VALUE! errors. I'd like to replace all of these cells with NA.
As part of a macro i do a find and replace on a range cells that replaces "/1" with nothing (""). Therefore 2/1 becomes 2, what i don't want though is if the de-nominator includes a 1 but not only 1 to keep the whole of the de-nominator, say 8/11 does not want to become 8/1.
View 4 Replies View RelatedIs there a way to define the end of a cell in a find/replace? I.e. I want to find characters like this:
;;[space character][end of cell]
and replace with
[end of cell]
(that is just delete the ;;[space character] bit when it is followed by end of cell)
[Code] .....
Trying to repeat a 550 or so character statement with a find/replace however I am getting type mismatch errors. When I use a smaller message in the "replace" it works.
I need it to post a message exactly as long as what I have in there. How do I get it to work?
Need assistance with the code for catching errors when using the find / replace function in excel? In particular, I am trying to write code to break to an error message when the value or string searched for isn't found in the find / replace. At the minute I have just copied the standard code using a macro and all this does is return a message box saying X entries replaced.
View 3 Replies View Related multiple search match and replace content in a different column so for example
new workbook (look up table)
sku
search1
search2
[Code]....
so something like where you compare two tables and find and replace based on another cell that matches in my sku.. more details would be if the table column aren't exactly matching but the column header and the row header would match and fill or replace in the correct/corresponding cell is there a macro or vba to do this job in excel?
I'm faced with receiving data for time, in a text format, as follows:
Example of possible cell contents...
20secs
40m20secs
2h40m20secs
I'm not interested in the secs value but need to isolate the h (hours) and m (mins) values to use in another formula as numbers. The time could be shown in either of the above formats with either; (a) just secs, (b) mins & secs or (c) hrs, mins & secs... and of course the hours or mins values could be 1 or 2 digits in length. How can I determine (using formula only, not vba) what the values for hours and/or mins are dependent upon what is present?
how I could find 1 character and replace the entire cell. For example, it would find "<12", "<3", and "<32" based on "<" and then replaced that entire cell with "N/A".
View 2 Replies View RelatedI have a row of 2900 single letter (middle initals) however 222 users have no middle inital. this is a password scheme and need 7 digits, without the middle inital i only have 6. so I want to replace all 222 cell that are blank with a dash can this be done without doing each by hand?
View 4 Replies View RelatedI have a formula in cell J2:
='H:Folder[CI10001G.xlsm]Sheet'!$C$5
I need a macro that will change the part of the formula that is "CI10001G" to the value of cell A2 which would basically be something along the lines of "CI10004D" so the formula would change to the following:
='H:Folder[CI10004D.xlsm]Sheet'!$C$5
And then this formula will then successfully look in the newly directly file path.
I'm trying to work on a macro that will copy two cell ranges (name of current month and prvious month) from one location, store it as a variable and use that variable to find replace similar text string in another tab. Below is my code which is not changing the values in another tab...
Dim OldMonth As String
Dim NewMonth As String
Sheets("X1").Select
[Code]....
Is there a formula that can find a keyword within a cell then replace it with a formula that will add the adjacent cell value up.
A1=103 B1=104
A2=104 B2=105
A3=105 B3=Out-1p-(1)
A4=110 B4=111
A5=111 B5=112
A6=112 B6=113
A7=113 B7=114
A8=114 B8=Out-3p-(23)
I am looking for a formula that can automatically calc (B3) to 106 (based upon cell A3) if the cell has a value of (*out*).
I want to have a macro that will look at a few cells say A1 & A2 and then use the contents of the cells say 1Q07 & 2Q07 respectively to do a find and replace find 1Q07 and replace it with 2Q07. I want it to use the contents of the cell and not just the text because the contents of the cell will change each time used. I tride to do it here but I dont have it yet. It doesnt seem to pass the value to the variable and the variable doesnt seem to do the find and replace.
Private Sub Update_Click()
Application. ScreenUpdating = False
Dim quarter As String
quarter = CStr(txtquarter.Text)
txtquarter.Text = CStr(quarter)
Sheets("Income Statement").Select
Dim oneq As Single
Dim twoq As Single
Dim threeq As Single
Dim fourq As Single
Dim fiveq As Single
oneq = ActiveSheet.Range("A2").Value
twoq = oneq - 1
threeq = twoq - 1
fourq = oneq - 4
fiveq = oneq - 5
Replace characters in a string. I have this macro:
View 2 Replies View RelatedA range of cells contain characters that i want to replace with spaces. The problem is that the length of the string differs and also the characters differ.
For example, below a list of cell values:
GR
GRKR
GRKRKR
GRKRK1
GRON
GRONBL
As a result for the first value i want 2 spaces, for the second one 4 spaces etc.
Is it possible to do this with a formula? I don't want to use VBA for this if possible...
I'm trying to do a quick find and replace across a range of cells to remove Chr(10) and Chr(13) characters. For some strange reason, some of the cells are being good and duly allow the removal of said characters and some are not - they stubbornly refuse to remove with no error message. The ones that are not allow removal if I copy the individual cell to a new sheet.
Sub removeCharNew()
'Dim x As Integer
Cells(1, 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Cells.Replace Chr(10), ""
Cells.Replace Chr(13), ""
End Sub
Is it possible to display a dialog box or msgbox that doesnt have an OK button ?
i.e I want a message that comes up on the screen that says "Links Updating...Please Wait" which then automatically changes to "Links Sucessfully Updated" on completion...I dont want the macro to be interrupted by the msgbox/dialog...