Hi
I'm on a Mac with Excel 2008
I have a contact list with 6000 rows, I need to alter the data in two columns - 'contact name' and 'position' where there is more than one line of data in a cell.
For example...
If ROW 1, COLUMN A contains the following:
Mr Smith
Mrs Smith
and ROW 1, COLUMN B contains
Director
Sales Manager
I would like to delete Mrs Smith and Sales Manager. I'm hoping there is a formula that can find all cells with a return or multiple lines and delete the second line?
Using Edit -> Replace
Highlight the data, run Edit -> Replace
With Find What dialog active hold ALT and type 0010 with the numeric keypad (on laptop hold ALT + FN and type 0010 on numeric keypad [letters]) followed by *
Leave Replace With dialog empty and press Replace All
Using Formulae (if preferred)
C1: =LEFT(A1,FIND(CHAR(10),A1&CHAR(10))-1)
copied to D1
C1:D1 copied down as far as required - copy results over originals (values only) and remove C:D
(you have no VBA support in XL2008 so the above are you best options)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks