?
Solved

How to un concatenate data

Posted on 2013-02-06
2
Medium Priority
?
857 Views
Last Modified: 2013-02-06
I have the following information in one column: St900Wk5Day41273
I need to have 6 separate columns: St  900 Wk 5 Day 41273
I can't select the column and use the Text to Columns option b/c some of the store numbers have 4 digits, for example: St1326Wk8Day41298
so it'll have to be St 1326 Wk 8 Day 41298...Is there a formula that can separate it correctly?
Thanks!
0
Comment
Question by:Ladkisson
2 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 1400 total points
ID: 38860396
Formulas from left to right for your six columns (assuming only the store number shifts from 3-4 characters)

St
=--mid(a1,3,find("Wk",a1)-3)
Wk
=--mid(a1,find("Wk",a1)+2,find("Day",a1)-find("Wk",a1)-2)
Day
=--mid(a1,find("Day",a1)+3,len(a1))
0
 

Author Closing Comment

by:Ladkisson
ID: 38860457
Wonderful! Thank you!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question