Previous Topic Next Topic

Find Value between characters in one cell

[Solved/Closed]
Report
Posts
3
Registration date
Wednesday May 4, 2011
Status
Member
Last seen
May 5, 2011
 -
Posts
3
Registration date
Wednesday May 4, 2011
Status
Member
Last seen
May 5, 2011
 -
Hello,

I get this report-list of personal information, in text format (TXT)
Each area is separated by |

Name|Experience|Age|Sex|Location|Contact
Amy|6 years|28|Female|Address|Mobile
Fred|4 years|33|Male|Address|Home-Fone
Victor|8 years|37|Male|Address|Home-Mobile

...and so on.

Then I copy and paste it in to excel cell.
But ... after that i have to organize every area into their separate cell constantly.

Is there any formula, for each cell can find the word or value, between the characters |...|...|...|, and then automatically copies them to their respective cell.

Example;
[CELL] from the LEFT________________-________________{CELL} with pasted TEXT
[Name] [Experience] [Age] [Sex] [Location] [Contact] - {Name|Experience|Age|Sex|Location|Contact}

[Amy] [6 years] [28] [Female] [Address] [Mobile] - {Amy|6 years|28|Female|Address|Mobile}
[Fred] [4 years] [33] [Male] [Address] [Home-Fone] - {Fred|4 years|33|Male|Address|Home-Fone}
[Victor][8 years][37][Male][Address][Home-Mobile] - {Victor|8 years|37|Male|Address|Home-Mobile}


...Thanks

2 replies

Answer 1 / 2

Select column A, assuming that is where the data is, then choose the Text to Columns feature. Choose Delimited and choose the | (pipe) as the delimiter.
Unless some of the fields need special treatment regarding the data type, just click on finish.
Answer 2 / 2
Posts
3
Registration date
Wednesday May 4, 2011
Status
Member
Last seen
May 5, 2011
Thanks RayH
That helped me a lot.
Ask a question