Solved

How to un concatenate data

Posted on 2013-02-06
2
797 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 350 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

932 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now