Separating text & numbers in different columns

[Shikha S]

Hi,

I have to separate text & numbers in different columns.

e.g. 349,910 American Capital Ltd. (a) 4,902,239

i want all 3 data in separate columns, there is a series of data like this wherein numbers are there both at begining & end & text in middle. Can you please suggest on this.

[Pete_UK]

Show some more examples of the data that you have, or better still - attach a sample workbook.

Pete

[Shikha S]

Hi,

I am attaching a file. Please refer to it as an example. There are thousands of entries like this.

[Shikha S]

Just to add to my previous post, i am attaching 1 more file in which i have shown how i want the final entry. So you can refer this as the latest one.

[:) Sixthsense :)]

Use Text To Columns

Data>>Data Tools>>Text To Columns

[Pete_UK]

See attached file.

Hope this helps.

Pete

[Shikha S]

Thanks a lot for your help, it is working great. But din't got the formula. Please explain me the 3 formulae.

[Pete_UK]

The first formula extracts the characters up to the first space, sees if that is a number and if it is it returns it as a number, otherwise it returns an empty cell. The third formula does the same, though it looks at the characters after the last space. The middle formula removes both the first number and the last number from the string, and then removes any leading or trailing spaces.

Hope this helps.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[Shikha S]

Hi Pete,

The 3 formulae you gave me earlier are working great, however there is another kind of data in which i need help. Can you please help me with the attached data. Problem is same breaking text & numbers into different columns. Please use Separating text & columns file.

[Pete_UK]

I can't help you on this, as I'm just about to go away for a few days - back Friday evening.

Pete

[Shikha S]

Hi Pete,

Can you help me with this data by separating all 5 data types in 5 different columns.
Attaching file for your reference.

Thanks,

Shikha

[Pete_UK]

See attached file, which parses the data and bunches the records up.

Your data is fixed width, so you could easily have used text-to-columns, and then have a bit of manipulation afterwards.

As this did not follow on directly from your earlier postings, you should have started a new thread.

Hope this helps.

Pete