Remove Specific Text From Only Certain Cells
I am exporting lots of data from a website and for some reason to do with html i am getting &nbsp; in a few cells in my description column (column C), i think that means "& " in html. this occurs in some cells but not others when i paste it into Excel. I would like to remove these characters.
I know I can do it with crtl H and the replace field blank but I would like a formula so in the future the user will simply have to paste information and then get converted information out (as they will struggle to use excel).
View Complete Thread with Replies
Related Forum Messages:
Remove Numbers From Cells With Text
I have copied information from an outside source to Excel. Unfortunately, the information includes numbers preceding the text which I need to delete for all records. Is there an easier way of deleting this information without going to each individual cell to delete the numbers
Formula To Remove Text From Cells
I require a formula to remove all text and spaces from a cell, reporting back only the single digit number that is contained within.
A1 - Data to be manipulated (e.g. options look like "2UE", "UE2", "4P", "5 TW")
A2 - Result with all text and spaces removed (e.g. "2", "2", "4", "5")
Remove Text From Multiple Cells
I need to remove the last bit of text from the contents of multiple cells in a column. In my attached example, I want to remove the last line break and the text " List Price £", by processing all the cells in the column at the same time.
I can add text OK in Format: Cells: Number: Custom using @"text" but haven't been able to figure out how to remove text yet!
Remove Part Of Text From Cells
I have a simple sentence in cells C3, D3 and E3. The sentence in C3 is:
Find Airfare to Chicago, Airline Tickets to Chicago, and More | XXXXXXXXX®
D3 contains: XXXXXXXXX highlights outstanding airfares to Chicago from quality airlines and agencies. E3 contains my list of keywords: Chicago Flights, Cheap Flights to Chicago, Cheap Chicago airline tickets, chicago flight, chicago airfare, chicago airline ticket, chicago airline tickets
The XXXXXXXXX in the C&D cells is the client name which I had to blank out in order to post here. I wasn't sure if you guys would need to know exactly how many characters there are in each cell. In column B3 is the name of the city, in this case the city is Chicago. I have 8000 destinations so I need a formula that will take the city (or any information) in the B column and replace that in the C, D and E columns in the appropriate place. I've attached a small, representative file to demonstrate.
Remove Duplicate Text From A String In A Row Of Cells
I have a large worksheet and I managed to duplicate data in a row of cells.
In the string above I need to delete all text from the first comma to the end of the string so the above would look like:
This is an example in one cell of many. The text prior to the first comma may or may not repeat and it is the only portion of the text I wish to keep. The first comma is not always in the same place (character count position).
Remove Text Character From Cells With Numbers
I want to change the character ~ with . in order to be able to make them numeric values to be feeded to other functions. But REPLACE seems not doing the job so I've been checking out other options such as seperating after and before the character ~. Details are below. I've been trying to use this formula to extract values from a delimited database which I open with excel. The formula that has brought me close is =IF(ISNUMBER(E51)=FALSE,LEFT(E51,LEN(E51)- FIND("~",E51)),E51)
When I import the database, the figures above have originals as 14010.00000 & 3210.00000 but transfer to excel as above. As far as I have observed 9 character spaces are displayed & the DOT transfers to ~ for some reason. I need the LEFT section of the ~
Remove Leading Single Quote From Text Cells
I am a SAS programmer and often use SAS Proc Export to dump data to Excel. Sometimes the data is an Excel formula, e.g.
Because the data is text, what get's put in the cell is '=HYPERLINK("#Sheet1!r1c1","click here") (note leading single quote).
I cannot use the replace function to edit them out, so must hand edit each one out -tedious at best.
Finding Cells Containing Specific Text By Searching
The easiest way of explaining what I'm after is to say, I have letters of the alphabet, in their own cells, and I want to find them by way of a search. I don't mind how this is done, but it would be good if for example you entered A, C and E, any cells containing those letters changed, maybe became bold, or the cell filled with colour.
Looking Back Through Cells In A Column To Find Specific Text(s)
In the attached sheet I need to check on the Destination in column B, and if it matches set criteria, divide the figure in Column E by 2. eg If LAX is the destination, then for any rows directly under LAX until the next destination appears, column E needs to equal column D divided by 2. If the Destination is not LAX, then Column E equals Column D.
the difficulty I am having is that there can be a variety of row numbers for each destination. I am having trouble creating a formula that can basically work backwards from Row-1 in column B until it gets to a text string, and check that text string against the criteria. so it may be row -1 up to about row -9 dependant on the number of flight times available to each destination throughout the week.
Macro To Search Cells For Specific Text Segment
Im sure this has been addressed before but i need a macro that would act like a "Contains" function. I have Column B that has user comments. These are sentences like "I have a billing issue" or "My item is damaged". My goal search these for keywords and then have a value (category) populate in Column C
Heres an example. the Category is Damaged Goods: Keywords associated with it are "broke" "broken" "replacement" "damaged" "not working"
Another category is Billing: keywords include "bill" "credit" "account"
I need a macro that will search all the comments and if "broken" (for example) is found anywhere within the comment, the value "damaged goods" will be populated in the same row, column C
Remove The Specific Values From Cell
u can see in that no last part like this.-1,-2,-4
i need to remove that part.i have lot of no like this.i am going to do this manually it will take long time.pls some body can make macro or something for this.
answe should be like this.
Find And Remove Specific Information From Column
I have a column 'AE' that contains the html code for ecommerce item descriptions. While importing/exporting back and forth with another program and my spreadsheet, the following was added all throughout the descriptions.
In some places, it may be three or four times consecutively
Is there a macro script I could run to simply remove this any time it occurs in this column? It is just showing up as text and I am sure in another program it was representing something other than text, probably a space or hard return. Regardless, I don't need it.
See example below: ....
Macro To Copy Specific Cells From Row From Source & Stop When Next Row Cell = Specific Value
I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)
After that then start all over again unless the next rows cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.
Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.
Sheets("ee template").Copy After:=Sheets(3)
Specific Cells Populate With Specific Numbers When A Value Within A Range Is Entered
Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.
Macro To Only Show Rows That Contain Specific Text/Chinese Text Issue In Visual Basic ( 2007)
I want to create 3 command buttons (active X) on a worksheet to toggle between showing rows which only contain the below text in column L (range L9:L30) and showing all rows containg the options (However, I also have some blank rows in this range and i always want them to remain hidden.)
My text options are:
The text arrives in the cells via a VLOOKUP
Is the chinese text a problem? i can't type it into VB.
I've been using the following macro to hide and unhide rows with a command button in the same sheet:
Private Sub CommandButton1_Click()
Dim rngCell As Range
Dim TakeAction As Boolean
If ActiveSheet.CommandButton1.Caption = "Hide" Then
TakeAction = True
ActiveSheet.CommandButton1.Caption = "UnHide"
TakeAction = False
ActiveSheet.CommandButton1.Caption = "Hide"
For Each rngCell In ActiveSheet.Range("I9:I30")
If .Value = 2 Then .EntireRow.Hidden = TakeAction
VLook Up Specific Text Within Text And Output Match From Table
I like to go to Berlin.
You like to move to Amsterdam.
They want to see Chicago.
When will the reach San Francisco.
What I want to do:
I want my function to see if the text in Table 1 contains a value in Table 2, and if yes, output that value.
I like to go to Berlin.
You like to move to Amsterdam.
Highlight Specific Text Within A Cell Of Other Text
This is the text:
Take 5 PPE Swabs per Area, Both Shifts. Test various equipment - hands, aprons, sleeves, hats, etc.
What I need is for "Take 5 PPE Swabs per Area, Both Shifts." to be bold and highlighted in gray, but none of the other text. Conditional formatting highlights the entire cell, which won't work.
Extract Specific Text From A Text File
I had VBA run a third-party program ("HYD") using data listed in my spreadsheet. HYD produces a textfile and I want to extract a number from that log (line 58, columns 6-10 to be exact).
I started with
Open "C:TempHYDLOG.TXT" For Input As #1
but I don't know how to have it skip down to line 58 or which function to use to extract the number I want. I've read through tons of help files and forums but none of them treat anything as simple as scrolling down to specific lines.
The entire subroutine up to that point is as follows: ....
Remove Text Header
I have two txt file where the report is being generated but importing it in excel and using text to column distorts the data plus it has a header which repeats on every page... how can delete those header and tidy the sheet up. I have attached the txt header example.. Any help on this.
Add Or Remove Text Within One Cell?
I need to format C2 so that it removes all text that comes after a dash in B2, but if no dash exists, then it returns the text: Parent. For example, here are 3 values in B2, B3, and B4:
In C2, C3, and C4 I'd like to have the following values returned based on the above values in B:
Remove Spaces From Text
I'm trying to use the SUBSTITUTE function to remove spaces from my cells in column A. The trouble is My formula is removing all the spaces from the cell. My formula is: SUBSTITUTE(A1," ","")
I do however have many cells that contain a space between characters 5 and 7 and spaces at the end of the cell which have been padded out to 15 characters long.Its only the spaces at the end of the cells I need to remove.
Remove Formating ' From Text
I often get spread sheets to work with that have a non visable/printable character (') in front of them. I haven't been able to find a way to get rid of them other than editing each cell and removing it. I am sure there must be a way. I have attached a small sample of the cells in question.
How To Remove Text After The Second Instance Of An Asterisk?
I'm trying to convert variable length strings which are being copied from a display and loaded into an array.
I have it working fine for the majority of the data, which comes in looking like "*ABC@US" or "*AABC@US"
However, some of the data looks a bit different, particularly lacking the @ symbol. So what I end up with is
"*ABC US*ABC US*ABC US"
What I need to get to is just "ABC US" FYI the US part can be 1-5 characters.
SO... I need a way to truncate anything after the second instance of the asterisk. Haven't been able to get it to work using various trim, mid, len, left, right, etc functions.
Remove 14 Digits From The END Of Text In A Column
I know how to remove the first part of a text, but not the end. I have a very large amount of data in which the first column contains a product description and then the UPC code. I need to isolate the product name from the UPC code into 2 columns. I used =RIGHT(B2,14) to isolate the UPC, but since the length of the product name varies, I'm not sure how to isolate the product name. I have looked at using the TRIM and TRUNC functions without any luck. 'Text to columns' doesn't work because I have multiple spaces, 'Text to columns' would work if I could used 2 digits as the delimiter, but the function doesn't allow that.
Can someone please suggest a function or formula for this?
Here is an examples of how the text appears in the field. (Note: I only need to cut off the UPC code, I need the measure information to stay).
some really long product name 12 oz 5 bag 09-99999-99999
another shorter name 2 oz 09-99999-99998
Remove First And Last Character From Text File
I'm trying to write to text file a HTML page that is in string variable sFullPage.
So far my code is like this:
Dim sFName As String ' Path and name of text file
Dim iFNumber As Integer ' File Number
sFName = "c: est.html"
'Get an unused file number
iFNumber = FreeFile
'Create new file or overwrite existing file
Open sFName For Output As #iFNumber
'Write data to file....
How do I remove those first and last two marks (a double quote on each side + square mark from the end)? Do I use somehow wrong data types or wrong printing methods?
How Do I Remove " From A Text String
I am trying to tidy up a stock control program called Autopart (v20).
The problem I have is that suppliers descriptions use the " character instead of the word inch when describing products. This causes issues when reporting or comunicating to Microsoft Purchase Plus Ordering System.
Is there an easy way that I can remove alll of the " characters from the description cells in a text string?
Remove Parenthesis & Text Within From Range
For a spreadsheet that contains info with opening and closing parentheses in the cells I'm testing on, I am having problems getting multiple IF statements to work together in the same formula. I can get them to work separately in different cells, but when I try to combine them in the same cell, I get the #VALUE error. Here is what I'm working with:
In cell G7 I have the text: Fort Fraser (1)
In cell H7 I have the formula: =IF((RIGHT(G7,1)=")")*AND(MID(G7,LEN(G7)-2,1)="("),LEFT(G7,LEN(G7)-4),G7)
In cell G8 I have the text: Fort St. James (11)
In cell H8 I have the forumla: =IF((RIGHT(G8,1)=")")*AND(MID(G8,LEN(G8)-3,1)="("),LEFT(G8,LEN(G8)-5),G8)
Both formulas work correctly by discarding the parentheses and everything in between, as well as the space before the opening parenthesis. As you can see, the first formula will work with a single digit in the parentheses, and the second works with two digits. It's just when I try to combine the two tests in the same formula (editing the second one so it also tests on G7) that I get the #VALUE error. I have tried using OR to combine the two tests.
Remove TextBox Default Text
Hopefully a really simple query:
I have a userform with numerous Textboxes and comboboxes for user input - however the end users aren't too PC savvy and I'm ending up capturing "Please Enter NameJohn Smith", "Please Enter Job TitleSales Manager" etc. My original Instructions told them to tab their way through the form, meaning each time they reached a TextBox their input would overwrite the ("Please Enter x") string prompting them for input.
Is there any way to replicate this "select all" on click (globally as there are potentially 50 to 100 textboxes and 12 varients of the form)?
Penfold Auto Merged Post;OK so I've made a little progress:
Private Sub UserForm_Click()
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
The above selects all the text in a textbox - I think I need to apply this to the textbox "on click" - however that could be achieved? Then it needs to be generalised - ie:
UserForm1.Controls("Textbox" & variant)
Remove Text With Brackets & The Parenthesis
I have a spreadsheet with 900 rows. All of the rows hold different information. The first column has contents such as this:
This is the text (please remove me)
This is some more text (I need to be removed too)
I want to remove from every cell, everything in the brackets - so everything from the first open brackets to the end of the cell contents). Every cell is a different length and the information within the brackets is different.
Remove 1st X Characters From Text In Range
I have a column of data; for each line of data I have something like ABCDEEast Anglia, ABCDFFarnborough. The text at the start is standard and all cases of East Anglia will have ABCDE prior to the East Anglia. Is there an easy way [aside from replacing] to loop through 1000 data points and replace the long method with a shorter concise version (i.e. East Anglia only). I have attached what I mean
Remove Multiple Characters From Text
This may be a very simple question so forgive me for my ignorance. I have text in individual cells that look something like this (not actually addresses but same format):
Doe, John 123, Anywhere St (Apt A), Anytown Anystate 12345
I have about 5,000 records. I would like to convert the records to look like this:
Doe John 123 Anywhere St Anytown Anystate 12345
Basically I want to take out all non alphanumeric characters and anything between curved or square brackets. In my minds eye my macro would read something like this:
Do until last character.
If character = alphanumericTrue Move to next characterFalse If character = spaceTrue Move to next characterFalse If character = curved or square bracketTrue Delete all text in brackets including brackets then move to next characterFalse Delete character then move to next character
Loop. I would of course create an additional loop to run down the 5,000 records.
Forcing Cells To ALWAYS Find MIN And MAXIMUM Values From A Specific Range Of Cells
I'm working on a project for my company. We make plastic tanks and for quality control we want to start recording the thickness of the tanks in different areas/zones of each tank.
Attached to this message is an Excel sheet that I've been working on. From "Sheet 1", it records inputted thicknesses into WorkSheet "1098". On the top of "1098", it shows all of the recordings, and just below that are the "10 Most Recent Entries".
Right below the "10 Most Recent Entries", there are formulas to calculate the Min and Max Values. Whenever a new entry is recorded, the selected cells for the Min and Max formulas change. Is there a way to force the cells to always stay the same?
Read/Import Text File & Remove All Chr()
I have a comma delineated text file that is full of stock item details such as stock code, description, 3 different prices etc it is about 15 fields wide by about 400 rows down. I need to be able to import all rows but only certain columns into an excel worksheet using vba. I know I can easily just rename it to .xls or .csv or something like that but its a text file we export out of our system at work and we need to be able to give it to customers who then can import into this excel spreadsheet/calculator I am creating.
The file looks something like this:
"String 1", ""String 2", "String 3", "String 4", ...... all the way up to 15
There is 400 or so rows (which represent stock items)
Now I need to copy all rows in column 1 (string 1) all rows in column 12, 13, 14, 15 (This is the only data I need from the text file in this particular spreadsheet/calculator)
I have it working fine reading the entire row in one at a time splitting it up into a string array using the Split() function and then only copies the certain array indexes i need into the worksheet here is the code for this: ...
Find Text Between Characters And Remove Hyphens
I need to make a macro that will find text between "o/" and "/", remove hyphens from the text it found, and then add it to the end of the current cell contents.
I know how to add to the end of current cell contents, but cannot figure out how to grab text between certain characters or replace hyphens and replace with spaces.
Remove Text Inside Parenthesis & The 2 Brackets
I am importing data into excel from another application. I place the data on a sheet and then use macros to re-arrage the data to another sheet. One column of cells gets populated with strings of the following format <text1>(<text2>)
I would like to extract <text2> and place it in a cell on another sheet. the length of text1 and text2 varies.