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.
Is there any way to remove the first part of a string of text in a cell and save the second part?
The first part of the text string is a team code that has a variable number of numbers, capital letters and sometimes spaces. The second part of the text string is a variable number of words in a team name that all start with a capital letter and have lower case letters. Every line has a different team code and team name.
The original spreadsheet also has a column with just team code. Is there a way of using this column to "subtract" the team code from the text string to just leave the team name?
I try to write the function that gets some "ref" and returns appropriate item. For example: if I give to function "C3", it returns "AAA" if I give to function "R18", it returns "BBB" (cause it between R15 to R26) if I give to function "R9", it returns "BBB" also.
Is there a formula where it will find all that reads 07-06 and be able to change it to 08-06? Below are samples of formulas where I am looking to change 07-06 to 08-06 without having to go into each cell and manually changing 07-06 to 08-06:
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.
I'm working with a large range of data in single columns and I want to remove all text characters (a-z) from the workbook. There are some cells which have text characters with numerical characters so I'm struggling to find a way to isolate them.
I have a list of data that populates B2:B2900 This data is often prefixed by a 'reference code' that I wish to be removed. Now rater than perform this manually aprox 3000 times is there a formula or some VB code that will complete this for me....
eg
The list of data is shown like:
SC7547-05 - Payne, Freda SC8706-08 - Rungren, Todd SC8714-05 - Travis, Randy SC7517-03 - Beach Boys, The Love Song Now You're Gone SC7512-01 - Horton, Johnny SC8721-15 - Journey
So I wish for the SC7547-05 - to be removed from the first example to just leave Payne, Freda and continue this throughout the list However if the data is found not to include this code (as in the 5th/6th examples above) leave it alone
So if the code of formula is run for the above the outcome would be....
Payne, Freda Rungren, Todd Travis, Randy Beach Boys, The Love Song Now You're Gone Horton, Johnny Journey
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).
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!
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
building a worksheet to list people, payrates and hours worked etc. the header needs to have fill in areas that change with the job so I entered them in the rows rather than in a proper header. what I want to do is get rid of gridlines in the first 6 rows but leave them intact on the rest of the document. I see templates that this was done somehow.
I need to remove duplicate Part Numbers where other information in the cells will not match. In the following two examples, the only difference is that the COMP_ID: number is different, but for my purposes, the second example is a duplicate and needs to be removed. I have hundreds of rows of this type of information with various part numbers: ...
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).
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
I have a column of cells, some contain text and some do not. The cells which contain txt or are left blank could change on a daily basis.
I'd like Excel to look at the column of cells which contain the data and populate the information into a list on another worksheet without the blank cells, can I do this?
How it looks now: Sheet 1, cells A1:A6 Henry blank blank James blank Bob
What would be sweet: Summary data on Sheet 2, cells A1:A3 Henry James Bob
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)
14010~000 3210~0000
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 ~
UserForm1 Current OptionButton names are like ABC123, ABC 124, etc.
How can i change all names, with code - as i am not seeing a find and replace option within UserForms, to all but the "ABC" part, the result of the OptionButton names shall therefor be 123, 124, etc. - delete "ABC" or, find "ABC" and replace with
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.
Initially I'm simply copying a data table from a web page using "Ctrl + A" then "Ctrl + C", and then pasting the data straight onto a new worksheet so I can work with it. (After temporarily re-naming the old sheet)
But I keep finding what looks like double-spaces after some of the important text within the Range of cells I'm working with. I need to be able to select & conditional format the values of the text in some columns of the sheet, so need to loose these trailing spaces.
Unfortunately, it's not consistence as to how many spaces trail the text I need. Sometimes it's only one space, sometimes its two spaces ?
So far, I've had mixed success with a recorded "Replace" code but none of the other codes I have found on forum pages either don't work all or seem to give any consistent results. E;g; TRIM, CLEAN
I suspect my problem is, I do not know how to call the code properly, or trying to work with too large a range ?
I have 800+ files the problem is that the file name ends in 80 different combination so I need to try all of those for each file.
eg: one of the 800 is "109 st no 103 av" the file could be called: "109 st no 103 av nb1_cleaned.xls" or "109 st no 103 av nb 1_cleaned.xls" or "109 st no 103 av nb 1._cleaned.xls" or "109 st no 103 av sb1_cleaned.xls" . . . .etc
I wrote a code to try all those combinations, the issue lies a space the code adds before _cleaned, how to remove it?.
So the name should be "109 st no 103 av nb1_cleaned.xls" but my code is letting it be 109 st no 103 av nb1 _cleaned.xls
I have a large excel file with the top seven rows such as you see in the attached file. In row five there is a bunch of info before the month and the year. What I want to do is get rid of that info besides the month and year. I than want to sort each column by the month and year so that all the columns are in sequential order.
I have a column of data with letters in each cell, no numerical, only alpha. Now, some of those cells contain the letters "adj sub" as part of the text string in each cell. "Adj sub" is always at the beginning of the text string. As an example, a cell will look like this - "adj sub mhm". I want to delete rows whose cell description does not contain "adj sub" as part of the text in the cell.
I'm trying to create an Excel function that allows me to choose a row from a table based on text found within entered data. For example, say I have a table called "categories" that looks like this:
Categories cat dog mouse
Now in a different section of the workbook, I want to be able to enter an arbitrary number of sentences, one per row, that look something like: "It's raining cats." "The mouse is hiding."....................
