I have a customer list of 20,000 people and businesses in the same collumn. I have managed to sort out the first names into another column with the text to collumns function because the names were sorted by a ",". I am trying to move the business names into another collumn based on there being a " " in the cell. So I have one cell with:
John
and another cell with:
Quailty air
I am trying to move all cells that contain a " " (space) to the adjacent cell. Again theis collumn is 20,000 records longI am just looking for a cimple formula to copy down to accomplish this.
Can anyone help with this?
Thanks for your help
Hi,
Please upload a few examples in an actual workbook and in a separate range show what results you expect.
Richard Buttrey
If this was useful then please rate it appropriately.
Click the small star iconat the bottom left of my post.
I have a collumn like this but 20,000 records long, I have already separated first and last names:
KIPINSKI
BAILEY
GOODALE PROPERTIES
A & B PROPERTIES
ADKINS
I am trying to get the reslut of formula like this in 2 separate collumns:
MAGNUM PRODUCTS
_____________________KIPINSKI
_____________________BAILEY
GOODALE PROPERTIES
A & B PROPERTIES
_____________________ADKINS
We are a small HVAC company and we are converting our old dateabase to a new software and our old software had the business names and residential customer names in the same fields, so I am trying to separate them out because my new software has a field for business and customer names. i appreciate any help you can provide.
Last edited by nick614; 01-13-2012 at 08:42 AM.
Nick,
Your post is unclear - at least to me. You say you have a column (singular), but then say you have already separated the names - implying at least two columns.
In addition it's not obvious how you get to the two columns. What's the rule that says Kipinski goes in col 2, but Goodale Properties in col1? Is it the fact that there are always spaces in the column 1 values or something else.
As I said before, please upload the actual workbook - or if it's large at least a representative sample with all but a few rows deleted. We always find it easier to work with specific examples rather than confusing descriptions. Finally what are the "_______________" in col 1. Are these default 'text'?
Richard Buttrey
If this was useful then please rate it appropriately.
Click the small star icon
I just want to mofe any cells with a space in them to the left and leave the records without a space in them where they are. I think that is the easiest way to separate the names and business names. In my previous post the "____" was supposed to be an empty cell I could nto get the formatting correct.
TEST.xlsx
Thanks
Hi,
In C2
=IF(D2="",B2,"")
In D2
=IFERROR(IF(FIND(" ",B2)>0,B2,""),"")
Copy C2:D2 down as necessary.
Regards
Thanks[/QUOTE]
Richard Buttrey
If this was useful then please rate it appropriately.
Click the small star icon
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks