Seperate words/numbers in cell in one column and transfer to other columns

Hi I have this project i have been working on some time. What i need here in the end it a move of Words/Numbers from one column F.
This is an example from cell F4 Date. 1307030703 CM 6684852,01 Amount.
I need this to be copied like this to other columns
column A Amount. in this format 6.684.852,01
Column B Payment Type. CM need to be displayed as Credit and DD need to be displayed as Debit
Column C Entry Date. This format 03/07/2013
Column D Value Date. This format 03/07/2013

All is taken from the string up here in cell F4
I dont need any rows to be deleted.
Just this string to be copied to the free columns(A4,B4,C4,D4) left for cell F4

I have attached a worksheet infact with all the file(changed acc to Privacy) which have been edited allot, just need this last step.
Its all in sheet 2 in the test sheet

Please have a look

Sincerely
Abjac

Try putting the following code into a VBA module.

Please Login or Register  to view this content.

The way I coded the main driver loop in split_values is assuming there is data in each row. I copied the rows and removed blanks to test. You could change the exit condition as you need to for your situation. For example: if you know the end row you could say loop until x = <end row>

You can also set the formatting on the columns for the date and number to anything you want really.

Hope that helps.

Hi. Mccombsj. Thanks for this fantastic code. It basically do the job all the way. I set the range as you said to Loop Until x = 10000, because i will not remove any blanks.
I just have a problem with the formatting of the numbers. The dates works great, but the numbers i have problem with.
You can see the result after i run the code. I would like to have the numbers with thousend seperator so 6684852,01 will be 6.684.852,01
And i would always like to have the , and 2 decimal places. But so far i have not been able to make this. And some of the numbers also give the wrong value.

Have a look at the test sheet with my result.

Thanks

Sincerely
Abjac

HI I finally managed to solve the format problem. It took some time and was more difficult than i though it will be. So again thanks for the great code
mccombsj. Really great thanks

Sincerely

Abjac

My code for the format.

Please Login or Register  to view this content.

I am happy to help. I am happy you were able to get the formatting. Have a great day!