TheIronSheik said:
Walking Boot said:
TheIronSheik said:
OK, I'm having trouble with this and I know it should be easy. Normally, I'd get my solution to this by using Text to Columns and just doing it manually. But I'm trying to create a spreadsheet that will do the work for me. Here's my issue:
I have a cell that has info in it listed like this adrarae : arzfdbvae : 32rgewfd : wdewve4fgsww2
There is no rhyme or reason to the length of each quartet. I'd like to be able to separate that single cell into 4 unique cells with each entry between the colons, not including the colons. Like I said, normally I would just use Text to Columns. But my goal is to be able to just throw raw data into an Excel sheet and have it spit out what I need on another tab. Totally free of me having to do anything manually.
How do I go about this?
Can you switch the colons to commas and import it as a CSV? Or am I not understanding how you have the original data?
No. It's pulling from an application we use. It's a huge sheet with lots of columns. I only use a certain amount of columns. So my goal is to copy the data into another Excel sheet. Then, in another tab, I have formulas set up to pull only the desired data I need and put it in the correct order. Each column needs some finessing to get it to what I want. I can then just copy that tab and enter it into the report Excel sheet. I do this for a lot of reports. Saves a lot of time.
I've got this one down except for this last part. I need to take this one column and separate it into 4 unique cells.
This assumes the data always has a ' : ' (space, colon, space) between each value. Change the Sheet1!A1 reference to wherever your data is at.
I threw it together quickly, so make sure to test it before you trust it.
Cell 1: =TRIM(LEFT(Sheet1!A1,SEARCH(":",Sheet1!A1)-1))
Cell 2: =TRIM(MID(Sheet1!A1,LEN(A1)+4,SEARCH(":",Sheet1!A1,LEN(A1)+2)))
Cell 3 =TRIM(MID(Sheet1!A1,LEN(A1)+LEN(A1)+8,SEARCH(":",Sheet1!A1,LEN(A1)+2)))
Cell 4: =RIGHT(Sheet1!A1,LEN(Sheet1!A1)-LEN(A1)-LEN(B1)-LEN(C1)-9)