Split single cell data into multiple colums

i have a huge data, converted from image to Excel. After converting the image 
to Excel all the data are stored in one cell( A1). But i want that to be 
split into different colums

Example
A1
September 15,2006     Name    Email ID Father's Name       Address    City   
 State   Pincode     Phone1       Phone2     Contact Time    Area Code  City 
Code     State Code    Amount    Discount     Total      Occupation     Emp 
no      Remarks

The above headers are the data in one single cell, but have too many spaces 
between each word.

Help me get this resolved
-- 
Thanks and all your help will be much appriciated
0
Utf
1/28/2010 2:16:03 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
1927 Views

Similar Articles

[PageSpeed] 43

Hi,
Do data, text to column, delimited, check the space box

"Pradeep" wrote:

> i have a huge data, converted from image to Excel. After converting the image 
> to Excel all the data are stored in one cell( A1). But i want that to be 
> split into different colums
> 
> Example
> A1
> September 15,2006     Name    Email ID Father's Name       Address    City   
>  State   Pincode     Phone1       Phone2     Contact Time    Area Code  City 
> Code     State Code    Amount    Discount     Total      Occupation     Emp 
> no      Remarks
> 
> The above headers are the data in one single cell, but have too many spaces 
> between each word.
> 
> Help me get this resolved
> -- 
> Thanks and all your help will be much appriciated
0
Utf
1/28/2010 2:24:01 PM
With the data in cell A1 try the below formula in cell B1 and copy/drag the 
formula to the cells to the right...which will split the string with 3 
spaces...Adjust to suit//

=TRIM(MID(SUBSTITUTE("   " & $A$1& REPT("   ",6),"   ",
REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255))

PS: This is under the assumption that the individual headers within your 
data will have atleast 3 spaces between each header

-- 
Jacob


"Pradeep" wrote:

> i have a huge data, converted from image to Excel. After converting the image 
> to Excel all the data are stored in one cell( A1). But i want that to be 
> split into different colums
> 
> Example
> A1
> September 15,2006     Name    Email ID Father's Name       Address    City   
>  State   Pincode     Phone1       Phone2     Contact Time    Area Code  City 
> Code     State Code    Amount    Discount     Total      Occupation     Emp 
> no      Remarks
> 
> The above headers are the data in one single cell, but have too many spaces 
> between each word.
> 
> Help me get this resolved
> -- 
> Thanks and all your help will be much appriciated
0
Utf
1/28/2010 2:24:54 PM
Hai Jacob,

If you provide me your email id i'll send the some example data, so that i 
can have a clear solution
-- 
Thanks and all your help will be much appriciated


"Jacob Skaria" wrote:

> With the data in cell A1 try the below formula in cell B1 and copy/drag the 
> formula to the cells to the right...which will split the string with 3 
> spaces...Adjust to suit//
> 
> =TRIM(MID(SUBSTITUTE("   " & $A$1& REPT("   ",6),"   ",
> REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255))
> 
> PS: This is under the assumption that the individual headers within your 
> data will have atleast 3 spaces between each header
> 
> -- 
> Jacob
> 
> 
> "Pradeep" wrote:
> 
> > i have a huge data, converted from image to Excel. After converting the image 
> > to Excel all the data are stored in one cell( A1). But i want that to be 
> > split into different colums
> > 
> > Example
> > A1
> > September 15,2006     Name    Email ID Father's Name       Address    City   
> >  State   Pincode     Phone1       Phone2     Contact Time    Area Code  City 
> > Code     State Code    Amount    Discount     Total      Occupation     Emp 
> > no      Remarks
> > 
> > The above headers are the data in one single cell, but have too many spaces 
> > between each word.
> > 
> > Help me get this resolved
> > -- 
> > Thanks and all your help will be much appriciated
0
Utf
1/28/2010 2:37:01 PM
Reply:

Similar Artilces:

I cannot enter data in my Excel 2000 spreadsheet
Good afternoon dear members of the microsoft.public.excel.misc newsgroup. I need Your kindly help. I cannot enter data in my Excel 2000 document for one or other reason. I just clic on any of the spreasheet cells and type for word but it does not work. Any idea ? Many Thanks. Octavio Ovidio What else is going on? Are you using VBA code on the back side? Are there any Excel alert boxes open behind your main application window? Please provide more information. Alex >-----Original Message----- >Good afternoon dear members of the microsoft.public.excel.misc newsgroup. I >nee...

"unable to load personal/free data" MESSAGE
Problem since updating Office to SR-3 (yesterday) I keep getting this message: "unable to load personal/free data" It means nothing to me except that it's irritating. Having checked out HELP it seems to be something to do with letting other people know when I'm busy or free. I haven't got a need to let others know whether I am busy or free, I have never turned on this service. I assume it's to do with the upgrade to SR3. Can anyone help me to get rid of this? Regards Andrew ...

See if cell is in Range
Thanks for taking the time to read my question. I'm passing a string that is a cell reference to a function. In that function I want to determine if that cell reference is within a predetermined range. Not sure how to do that. Right now I have: Function CheckRange(TheSheet As String, TheCell As Range) As Boolean If TheSheet = "Sheet1" Then if TheCell In Range("B4:B30") then 'This line is red as it is incorrect CheckRange = True End If What do I use instead of "In"? Thanks, Brad Brad Use something like: If In...

Editing in a cell
I am having a problem with a newly created workbook. When I go to a cell that has data in it and try to add more data to the existing string I lose all the previous information. The work sheet that I am working in is protected and I have "edit directly in cell" checked in the options section. It is almost like my double click (as well as F2) are being treated as a single click. Thank you I figured it out. For some reason "Hide" is selected for the unlocked cells. "John English" wrote: > I am having a problem with a newly created workbook. > > Wh...

unhide data in pivot table
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I saw that this has been raised before but did not see a solution posted. Has anyone found a workaround? The issue again, I have created a pivot table and in it I ahve e.g columns with data for different years. I want to hide the data on one year and select thehide functionin the fieldsettings and it does disappear neatly, however, when I want to turn this back on it is not possible. It does seem like the cmd button should do the trick when i click it witht he year marked in the fieldsettings, however, it has...

Money signs appear in my Cell and I don't want them there
Im trying to enter the numbers 2.9 in cell like AD and it turns it into $2.90. That is not what I need, how do I stop that? Format as General instead of currency -- Regards, Peo Sjoblom http://nwexcelsolutions.com "kate" <kate@discussions.microsoft.com> wrote in message news:E9EEA936-437C-486F-A2D3-2385EF6BD2D0@microsoft.com... > Im trying to enter the numbers 2.9 in cell like AD and it turns it into > $2.90. That is not what I need, how do I stop that? ...

How do I get a cell to remember the last entry and add to it?
I am using Excel 2000. I want my cell to remember the number in it and add an additional number each time I need to retotal. Jab Sounds like you want that cell to be an accumlator cell. Simple question with complex results. You can have a cumulative total in a cell if you have a separate source cell for adding a new total to the original. Use at your own risk. I am Posting this just to show you how it can be done, not as a good solution. You would be much better off to have another column so you can keep track of past entries. Goes like this: =IF(CELL("address")="$C$4&...

BUG -- SUM after importing data
Hi, I've been trying to find some info on this bug but so far nothing has worked. I have copied a table from the web and am now trying to do so simple SUM functions. But it's not working. I have tried to import the data after exporting them (in case HTML is an issue), but nothing. I have tried some copy and paste special but didn't work. So please help. I have Excel 2000 on XP. Email me at oliviermoratin@yahoo.com Thanks again Hi try the following: - copy an empty cell - select your imported data - goto 'edit - Paste Special' and choose 'Add' "Olivier"...

Pivot Table
I have 2 lists with approximately 100 items/rows, with 5 pieces of data in each row. Each list has a common element (account number). Many times the account number shows on both lists, and I want to combine How do I combine both lists to use as source data in a pivot table? ...

Using Excell to input data into Access
Is it posible to use an excel spreadsheet to enter data into access? The actual reason for using excel to enter the fields in access is that over 100 of our users are familiar with excel, but do not know anything about databases. They actually call excel a database about 1/2 of the time. We work in a contract research facility that does lots of different projects in several scientific disciplines of varying complexity. I started using databases to store my research data in back in the 80's with db3. We did not even have an IT department until about 2 years ago. I have been trying to drag ...

Single Click Files
Realized that I can no longer single click to open files. Found at Folder Options the single click radio button is grayed-out now. Can this be changed? ...

Importing gain/loss data from Schwab
I can export such data in a csv format from the Schwab site to my PC. Is there anyway to get it from there into MS Money Plus? What I really want to do is get it into Taxcut software which seems to work with MS Money. Thanks Frisco198 <Frisco198@discussions.microsoft.com> wrote: >I can export such data in a csv format from the Schwab site to my PC. Is >there anyway to get it from there into MS Money Plus? What I really want to >do is get it into Taxcut software which seems to work with MS Money. Thanks Don't think so. Your gain/loss info in Money should agree wi...

Outlook data file check
Hello, I am using Outlook 2007 on XPSP2. Quite often on startup I get a message the Outlook is doing a file check and performance may be slow. I also often get on computer startup a message requesting me to send an error report to Microsoft that includes a few episodes of Outlook not responding. Any idea what this could be about and how to prevent both? -- Thanks, Bob ...

Help Freezing Multiple Rows
In earlier versions, you clicked below the rows you wanted frozen in place or to the right of the rows you wanted frozen. Now I see only how to freeze row 1. I have a main title in row 1, row 2 is blank and row 3 has column headings, so I want row 3 to stay frozen in place. I am having trouble accomplishing this and need help, please. Thank you. -- Virgo click on cell A4 then on the menu bar Window/Freeze Panes "Virgo" wrote: > In earlier versions, you clicked below the rows you wanted frozen in place or > to the right of the rows you wanted frozen....

looking for range of text in a single cell
I just started a new job and my company already had a file with abou 16,000 records in it (called Products). This file contains all of th parts numbers that they carry. In this file there is one column calle "description". This cell contains the dimensions, color name and som other information such as if the piece is flat or rounded. I hav another file that has about 400 records and this file tells me "colo name" as well as the "type" of stone that it is (called Stone_Type). What I am trying to do is add a field to the Products file that wil say stone type. ...

Report data in email
I have a Query with Email addresses and another Query with account numbers and dollar amounts. Does anyone know how to insert the data from the Query with account numbers and dollar amounts so that it goes to the individual email address? Thanks in advance for your help. Sandy It would depend how your database is structured. What tables do you have in it and what do they contain? Evi "SandySun" <SandySun@discussions.microsoft.com> wrote in message news:242BE193-0CBA-4CEF-9616-843180165FCD@microsoft.com... > I have a Query with Email addresses and another Query with a...

Data types
How do you mach the data type in excell with the data type in access? In excess I have have for SSN the category as special and type social security number, ans in Access I have the data type as" number". What can I do to get them to mach so I can update my records? Also with Zip code and Phone number in excel and "number" in Access. Andrew Hi it is generally accepted that zip code, phone number (maybe SSN, but i don't use these) are stored as TEXT in Access as you're not performing calculations with them and might like to store non-numeric symbols (e.g. t...

Merging excel data into word #2
Ok, I have a feeling this process is quite simple, but I haven't bee able to figure it out on my own, nor find anything in FAQ's. I have created an excel spreadsheet containing the following: students' last name, first name, ID number, advisor and counselo (obvioulsy Im in education) I would now like to create a phone call log for each student, but woul like to import each students' info into this word document. So as print the 100 or so pages, page one would have the first students info page two would contain the second students info, and so on. Any help is greatly appre...

To have a 'fixed cell' be equal to the last data entered cell in a column
Good Evening All, I have a worksheet, example below. I have frozen the panes to always show rows 1-3. I wish the cell A3 to be the same as the last 'non-blank' cell in Column A.(See explanation below). A B 1 2 Header Header 3__________________ 4 M1004 5 M1005 6 M1002 7 M1003 8 M1006 9 M1001 10 So in this case, at present, A3 would be M1001,(A9), but when I enter a new value in A10, (eg M1008), I would like A3 to automatically update to A10 ie M1008. I think that maybe INDIRECT or OFFSET maybe involved but am fairly unfamiliar with ...

calls to extract data from an open database
I have a workbook that on the “open” event connects to an Oracle database using ADO, this works fine. I need to code various calls to the data base to run several queries. How do I achieve this? Do I have to repeat the ADO connection string to the database complete with password etc; I am sure this is not necessary. Any code will be very gratefully received. -- with kind regards Spike The usual way to handle this is have a Public or Private ADO connection object so you can set that up once and keep it alive till you don't need it anymore. So for example: Opti...

Comparing columns and extracting data
I've got two columns with lots of data in each. Some unique, some the same. Short example: Col A cat cow dolphin dog fish horse snake zebra Col B bear cow dolphin dog fish hamster monkey zebra I would like to create additional columns with the following stipulations: Col C (what's common in both A & B) Col D (what's in A but not B) Col E (what's in B but not A) Any help is appreciated. These are all array formulas. **Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) > Col C (what's common in both A & B) Ass...

Multiple reports in range date
I have multiple reports using the same range of start and end date weekly. The date is display in a tab form to remind me the date range. I like to print weekly multiple reports using these range of date. Can anyone help me. Thanks. -- Message posted via http://www.accessmonster.com Create a form with two unbound textboxes to enter your dates. Use the textboxes as criteria in the query for the reports like this -- [Forms]![YourFormName]![TextBox1] and [Forms]![YourFormName]![TextBox2] Before running the reports open the form, enter the dates, leave form open...

extract the added data
This is a multi-part message in MIME format. ------=_NextPart_000_00AF_01C5AB00.52CE5210 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable We have a workbook with a large list of names and email addresses, = problem is that 2 people have copied the sheet and added extra names and = email's to each of there sheets. I need a way to extract the added data = so they can be added to the original. Is this possible? Sandy ------=_NextPart_000_00AF_01C5AB00.52CE5210 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Enco...

How do I have a cell update itself only if it's currently blank?
I need to track to retain the first value of a date field. Each time I load the worksheet the date field has the potential to change, I need to keep track of the initial date in addition to the current date that is in the field. I have date columns called ASSIGNED and 1st ASSIGNED. The initial value of both will be blank. At some point the ASSIGNED will be populated with a date. When it is populated for the first time I want to save this value in the 1st ASSIGNED column. I need something like: IF ( ISBLANK(ASSIGNED), "", ASSIGNED ) in my 1st Assigned cell, however, I do not ...

Changing Font Colour if cell is certain word?
Can I make the font of a certain cell change it's font colour to Red if the word in the cell is, example, "Fail"? I want the change to apply from B2:F23. -- wuming79 ------------------------------------------------------------------------ wuming79's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36037 View this thread: http://www.excelforum.com/showthread.php?threadid=560169 Use "Conditional Formatting". It's on the Format menu -- MartinShor Software Tester &amp; Musicia -----------------------------------------------------...