How to factor strings?

[GIS2013]

Hello. I need to factor strings like: "a_t(a) 2012-11-25 17:30:03.221 497 0". Currently the string is placed in one messy cell. I marked each data I need to copy to new separate cells. Problem is that I have many rows like this, and they are not similar in their size. So I cannot simply use functions like "Left". Any idea?

[elayaz]

Hello,

To understand your need, can you please attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[GIS2013]

Ela, please see enclosed file.

[XOR LX]

Hi,

I notice that all of the desired parts to be returned are separated by a tab character (CHAR(9)) in the original string - is this always the case, or have you manually inserted them yourself to try to illustrate your point?

Regards

[elayaz]

Similar to XOR LX question, do you copy paste the original date details here. Because when i copy paste the input date into notepad, it has lot of spaces in between.

[GIS2013]

Hi XOR,

I really don't know, sorry..

To me the original string looks like a mess. All I know is that I need to separate it into meaningful columns in order to work on it with the pivot tool.

[GIS2013]

Oh yeah - sorry, didn't understand XOR's question - I did copy pieces out of the original string.

[elayaz]

Ok. If possible can you attach a sample of the original string, that is before you copy.
desensitize any data..

[GIS2013]

I hope this will help.

[XOR LX]

Ok, so back to my original question: I presume that this string has been pasted/exported from some other source? Will this always be the case, i.e. will your data in Excel always be of this form, as a result of exporting/pasting?

If that's the case, then we can use this tab character to split your data. With a string in A1, enter this formula in B1 and copy across to the right as sufficiently far as necessary:

=TRIM(MID(SUBSTITUTE($A1,CHAR(9),REPT(" ",255)),255*(COLUMNS($A:A)-1)+1,255))

I have no idea how you are translating the 0 as "Sunday" in your desired outcome, nor where the final month string is to be found.

Regards

[elayaz]

Question2 excel file doesn't help much.

So I guess, they way to deal this is.

Copy the original date / column from excel.

Open ms-word, new document.
Select paste-paste special -formatted text (RTF)
Select the text with ctrl+A,
go to insert ->table-->convert text to table.
Now you can see the text has been converted into columns.

Copy the table from ms-word and paste back in excel. Make sure to adjust the excel column formats per the need.

Did that work?

[GIS2013]

First thanks,

Ok, so back to my original question: I presume that this string has been pasted/exported from some other source? Will this always be the case, i.e. will your data in Excel always be of this form, as a result of exporting/pasting?

I am the end-user here, and I don't know what software the company that sends me the data uses. Usually I receive the data already separated into coulmns (route - like you did, but also time, day etc), not this time, and I'm having problem getting from them the data on time (in their usual form).

If that's the case, then we can use this tab character to split your data. With a string in A1, enter this formula in B1 and copy across to the right as sufficiently far as necessary:

=TRIM(MID(SUBSTITUTE($A1,CHAR(9),REPT(" ",255)),255*(COLUMNS($A:A)-1)+1,255))

This is very helpful but it extracts only the route name.. Any chance I could extract all the other parameters?

I have no idea how you are translating the 0 as "Sunday" in your desired outcome, nor where the final month string is to be found.

If I can get 0 in a separate cell, it's not a problem to write an If function - if 0 then sunday etc..

[GIS2013]

XOR. Now I get it. I just need to drag the function you've written to the right, and it brings out the parameters. You're gorgeous!

[GIS2013]

Ela, thanks to you too.

[XOR LX]

You're welcome!