Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


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!

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
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.

View Replies!   View Related
Remove Just Text From Cells That Contain Mixed Text
I have a column in which the cells contain values such as "012321 BGH YRK". Is there any way you know of that I can remove just the text from every cell and keep the numeric values? A mass operation because there are literally hundreds of these cells. I am using Excel 2001.

View Replies!   View Related
Remove Symbols From Multiple Cells
I have copied some data from a website. I need to plot these onto a histogram. (this I know). However, the values have a $ sign attached. Is there a way to remove a whole column of $ signs without having to do this one by one?

View Replies!   View Related
Remove Text From Cells Which Should Have Only Numbers
way to remove text from cells that should only contain numbers.
Example: 124 fcm 12 std

View Replies!   View Related
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

View Replies!   View Related
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")

View Replies!   View Related
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   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 Replies!   View Related
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.

View Replies!   View Related
How Do I Remove Predefined Text From Programmed Cells
I want B1 to copy from A1, but if A1 contains a certain text string, then remove that text string and keep everything else. For example, I want B1 to remove "ru" "la" "fm" and "mu" so if A1 is 5464ru-xl then B1 will return 5464-xl or if A1 is 36944la-s then B1 returns 36994-s

View Replies!   View Related
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.

MOT 1/F1,
B/P 1f2,
B/P 1f2

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:

MOT 1/F1

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).

View Replies!   View Related
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 ~

View Replies!   View Related
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.
=hyperlink("#Sheet1!r1c1","click here")

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.

View Replies!   View Related
Multiple Text Cells In One Cell On Multiple Lines
share a macro to copy cells from range C20:C300 and paste them in C2 on multiple lines. If possible two lines gap between each cell's value when they are pasted in C2.

View Replies!   View Related
Coping Text From Multiple Cells
Can excel copy text from multiple cells into one cell?

View Replies!   View Related
Merging Multiple Cells Containing Text
I need to combine or merge text from cells in 20 rows of data into a format able to be printed. This is evaluation data for a class of 20 students, where each row is one students response to the evaluation, with the last cell in the row containing their comments (text data that may be blank, or up to several sentences in length). Here's what I'm trying to do:

Combine all 20 cells into one cell that can be printed (with word-wrap turned on).
Add a blank line between each comment.
Disregard any blank cells so extra blank lines are not added.

View Replies!   View Related
Change Multiple Cells Text Vba
I have a Column of Numbers that i need to change to this format 000-000-00, except i can't use the Format Cells option. Most of the number are eight long but some six and five long. For example:

30025141 needs to change to 300-251-41

109241 needs to change to 001-092-41

80493 needs to change to 000-804-93

The problems is i can't do it manually because there over 12000 numbers to change, so i need a vba code to do this.

View Replies!   View Related
Write Text To Multiple Cells Using Variants
I have created a sheet which gets populated by information but have left out the top row. I want to use the tope row to enter my header fields but was looking to see if there a more effcicent way of doing this.

I am using the following code below but was wondering if there was a way using arrays as variants or ranges to do it more efficiently.

View Replies!   View Related
Concatenate Text From Multiple Cells Into TextBox
I have a text document disaggregated into sentences each in different cells. I need a way of selecting different cells with a mouse, and through a Macro, have the text from those cells viewed in concatenated form into new textbox generated by the Macro.

View Replies!   View Related
Join Text From Multiple Cells To One Cell
i know this is easy but i don't have an idea how to do it. I have dates written on column A1:A7 like


what I would like to do is how can I combine 29-Mar-09 and 4-Apr-09 into one cell...I would like the output to be placed in cell A8 appear as "29-Mar-09 to 4-Apr-09".

View Replies!   View Related
Editing Existing Text In Multiple Cells Macro
i have a list of about 20,000 phone numbers that I need to edit.

all start
01 123456
01 123457
01 123458 etc

I am trying to create a macro that will change them to

+001123458 Etc

However, no matter what I try it always comes out as


F2 Copy and paste is going to take me forever, and I am sure that there must be a quicker way to do this.

this is my first time creating macros so am completly lost.
I want to keep the text that is in the cell that I am editing, just add the country code and delete the space.

View Replies!   View Related
Match Date & Multiple Cells To Various Colored Text
I'm trying to have a date selector dropdown box added on sheet as where indicated. This will have an option of last 1-10 draws which will trigger from cells a4:a13. Next, I need a submission button put on sheet as where indicated. When pressed it will give results in cells m4:n29.

Now the match part will lookup the source #'s entered from L6:L11, note these source numbers are repeated for match lookup for each date. The match should look for matching numbers derived from cells c4:j13. If a match is found it will then display the colored text as indicated in my sample, if no match is found it will display " No Match " in black text.

View Replies!   View Related
Remove Multiple Data By Multiple Conditions?
1. I have 4 columns (let's say D,E,I,J);

Account1 Sum1 Account2 Sum2
151 93,79 BF2479 30,00
1476757 93,79 BF7 22,00
BF2479 30,00 151 93,79
86041 84,03 23235 30,00
534 6608,45

2. I need to clean the multiple identical data in the list by both Account no. and Sum (of course the list is much longer!) so that the list should look like this:

Account1 Sum1 Account2 Sum2

1476757 93,79 BF7 22,00

86041 84,03 23235 30,00
534 6608,45

I must say that I am a beginner in Excel, so I tried to use this:


for column E, and


(which is reverse of the first one) for column J; (The ranges are the ones used in the real sheet), not for the ones in the example above!); The formula does seem to eliminate a dew unwanted data, but does not give me all the wanted results!

View Replies!   View Related
Fill Userform Textboxes With Text Combined From Multiple Cells
I am taking a range of cells (C22:D67) on several sheets ( same cells on each sheet) 4 sheets in total, each range appears in it's own text box on the single user form.

- I would like to know if there is an easier way of doing this, and can I leave out the cells without anything in them?

The code I am using at the moment is..

Private Sub cmdSeeNotes_Click()

Sheets("Core").Activate ....

View Replies!   View Related
Create Multiple Text Files From Cells & Save As Name In Adjacent Cell
I have two columns one of which has filenames and the other having values. I need to creating a macro that takes each value from a cell outputs it into a text file and then saves it with the filename of the cell next to the value. I'll need it to go down the columns and create separate text files for each.

View Replies!   View Related
Consolidate/Remove Multiple Instances Of Same Value
I have a column of IDs (260, 266, 405, 437, etc.). There are several instances of the same ID throughout the column. I would like to remove all but one instance of an ID so that I see what IDs I have. Is there a way to identify what IDs are there and then remove all but one of them?

View Replies!   View Related
Remove Password From Multiple Files
I Need To Remove Password From All .xls Files ( More That 2000 Files )
From A Directory And It's Sub Directory, All With Same Password.

View Replies!   View Related
Remove Duplicates Of Multiple Columns
I have searched the many forum solutions for duplicates however the ones I found relate to a single column. However what I would like to be able to do is starting from row 6 is to remove duplicates of multiples columns of a single worksheet, namely columns M, O, P, Q and R. Note some cells in each column are blank.

To clarify - it would firstly check for duplicates in column M, keeping just one of the rows of the duplicated value and removing entiely the row/rows of where the duplications appeared. Then go through each of the other four columns. of the worksheet doing the same for each of the other four columns.

Then after the duplicates are removed from all five columns provide a tally at the bottom of the worksheet of how many times a value now appears in the cells of each of repective columns.

View Replies!   View Related
Remove Autofilter From Multiple Worksheets
I have a single workbook with multiple worksheets all using Autofilter.

Is there a simple way i can turn off the autofilter in all worksheets in one go without referencing any particular worksheet?

View Replies!   View Related
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.

View Replies!   View Related
Remove Text From Cell
In a cell there is text and numbers, example: ABC123. In an other cell I want
to show the numbers only (123).

View Replies!   View Related
Remove Space At The End Of The Text Value
I have copy some data from other program which when paste, it give as text value with few space at end of the text value ie 120@@@ (@represent spacing).

I try using text to column to delimited the space but it not working.

View Replies!   View Related
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:


View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
Remove X Close On UserForm. Multiple Versions
I've found a link on OzGrid "How to remove the (X) CloseButton from Userforms" but I can't seem to get it to work with Excel 2003. The code looks like it was written for Excel '95, '97 and 2000.

View Replies!   View Related
Remove Multiple Selection From A List Box After Transfer
below is an excel file with a UserForm.

The goal here is to select the worksheets in one list box and transfer it to another. Multiple Selection is allowed. I am having 2 question i am struggling with.

Question 1 -
When passing the sheet names from List Box 1 to List Box 2 (using Add button) i would like the selected items of List Box 1 to be deleted after transfer. I thought i could access this using the command below but it does not work properly -> error message.

UF_PI_LINK_1.LB_SheetList.RemoveItem UF_PI_LINK_1.LB_SheetList.List(i)

Question 2 -
When the user is done the ListBox 2 need to be stored. When I save then close my workbook and then reopen it, i would like to be able to access the selection that the user has made before closing the worksheet. How can I store the content of ListBox 2 to achieve such a result. I guess i could use a use a hidden Worksheet where i could store the selection into some cells but i would like to know if there is a pure VB solution.

View Replies!   View Related
Remove Files Contaning Text
Im trying to loop through all the files in the folder. and look at the filename if the filename contains JPE_s.jpe delete the file.

View Replies!   View Related
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


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.

View Replies!   View Related
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

View Replies!   View Related
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:

Sub wrtHTML()
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?

View Replies!   View Related
Remove Comma From Text To Numeric
I need a simple way to extract the comma in 14,656 imported text so I can do a vlookup against 14656.

View Replies!   View Related
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?

View Replies!   View Related
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.

View Replies!   View Related
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)
End Sub

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)

View Replies!   View Related
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.

View Replies!   View Related
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


View Replies!   View Related
Want To Remove #div/0 Error By Vbnull String On Multiple Worksheets
criteria is bascially month and week based when i apply average forumla for a month i did get the avg for a month but when a record for tht month is unfilled
eg weekend is off so no values for that date that displays div/0 error.

I want a Macro which cud change this div/0 error in a selected range to be null
so it didn't reflect in avg function on final sheet and gives that value there. i know that it can be handle by updating the forumla


but i want this solution for 30 sheets for which in value filled range macro either deletes div/0 error or place null value there.

View Replies!   View Related
Remove The Number From A Cell Leaving Just The Text
I am trying to remove the numbers from a cell leaving just the text.

Say cell A1 contains Blk1Crd2 I would like say cell B1 to contain BlkCrd.

And preferebly I do not want to run a macro.

View Replies!   View Related
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: ...

View Replies!   View Related
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.

View Replies!   View Related
Copyright © 2005-08, All rights reserved