CODEWITHSUNDEEP
regexvbaexceldelimiter
Seeb
Seeb

Reputation: 309

Delimiting columns very specifically

I've got a column (with many thousands of rows) which I'd like to delimit into multiple rows. I have some experience using regular expressions in Excel, and I have some experience using delimiters in excel, but this one is just a tad too hard..

Let me give you three example-lines:

 - 23-12-05: For sale for 2000. 2010-09-09: Not found
 - 25-11-09: For sale for 3400. Last date found: 2010-07-08
 - 18-06-08: For sale for 5500. 21-07-09: Changed from 5500 to 4900. 16-09-09: Jumped from 4900 to 4700. 2010-02-04: Not found

Most other lines follow these structures. How can I create a new column based on just the first symbols before [COLON]; A second column based on the symbols between the first [COLON] and the first [DOT]. How can I continue to the last IF the text LAST DATE is not found? Finally: How can I use regex (or another way) to use the text 'NOT FOUND' to paste the last date into a new column?

Trust me, I have been at this for quite some time now (sigh). Any help is much appreciated!

Upvotes: 1

Views: 117

Answers (2)

Seeb
Seeb

Reputation: 309

By the way, this also works for me, to get the last date in a cell:

=LOOKUP(9999999999999999,FIND("**-**-**",A1,ROW($1:$1024)))

Only problem here is: I haven't the slightest clue what exactly I am doing here. For example, I'd like to use the same code to find the FIRST occurence of a date.

Can anyone explain this code to me? Why am I searching for a very high number? What is it in this code that makes that I find the last occurence? What does it mean that the 'starting number' is "row(1:1024)"?

Anybody knows?

Upvotes: 0

SeanC
SeanC

Reputation: 15923

you can actually use formulas for this.

Assuming the text is in A1,

B1: =LEFT(A1,FIND(":",A1)-1)
C1: =MID(A1,FIND(":",A1)+1,FIND(".",A1,FIND(":",A1))-FIND(":",A1))
D1: =MID(A1,FIND(".",A1,FIND(":",A1))+1,LEN(A1))
E1: =MID(A1,FIND("Not found",A1)-12,10)

(I'm assuming the date format does not change for the E1)

Upvotes: 3

Related Questions