Want to split the data in single cell to multiple column

Hi,

I have a data in excel which is converted from image file. Below is the conversion data.

A1 - jan 06'2006 Adam Paul [email protected] George M Wang 663 Southpaw Drive Rochester ME 77645 123456789 987654321 Evening at home VB123123 NY1231456 SS1534668 0.022 3600 2.5 Self Employed 6548795621

A2 - September 123 ' 2009 Yi Yan Si [email protected] micheal 6639 nothr street rochester ME 77546 213456647 21321321321 Any time VB1231231 NY23454351 SS54564654 0.044 1800 7.5 Self Employed 6545647987

Now i was trying to split the data into multiple column, but the problem i am facing is the spaces between each is not equal.

I tried text to column with space, but i got this

From A to AB

jan 06'2006 Adam Paul [email protected] George M Wang 663 Southpaw Drive Rochester ME 77645 123456789 987654321 Evening at home VB123123 NY1231456 SS1534668 0.022 3600 2.5 Self Employed 6548795621
September 123 ' 2009 Yi Yan Si [email protected] micheal 6639 nothr street rochester ME 77546 213456647 21321321321 Any time VB1231231 NY23454351 SS54564654 0.044 1800 7.5 Self Employed 6545647987

Please help me get this resolved.

Thanks and all your help will be much appreciated

Pradeep M

Hi,
your values are separated by several consequtive spaces.
So use Find&Replace to replace, let's say 3 consequtive spaces with a ; (check if this is not used in the data). After that you should be able to split the cell contents into columns using Convert text to column and selecting options Delimited and Semicolon

Originally Posted by bstoyano

Hi,
your values are separated by several consequtive spaces.
So use Find&Replace to replace, let's say 3 consequtive spaces with a ; (check if this is not used in the data). After that you should be able to split the cell contents into columns using Convert text to column and selecting options Delimited and Semicolon

Thanks for your respone.

Its really worked, but need to replace spaces, first need to find the maximum space and decreasing every time and replace it with ";"

Appreciated

Originally Posted by pradeepdeepu_001

Thanks for your respone.

Its really worked, but need to replace spaces, first need to find the maximum space and decreasing every time and replace it with ";"

Appreciated

No need to do so. When you set the delimiting character there should be an option so that Excel treats consecutive delimiters as one
Glad I helped.