Text - Number separator tool

[Rui Farinha]

Hello all,

I'm working on a tool that covers the text to columns limitation with spaces.

When the data is delimited by spaces, using balance sheets, it separates the names into multiple columns, which is terribly annoying.

This tool I've been working on, allows to go around that problem.

I have other sheets in my original workbook that allow other more or less information according to the users' desire, but the one attached is just an example.

I need help to solve two problems:

When the numbers have the dots from the thousands separator (in here we work with dots for thousands and commas for decimals), it messes the formulas (in column I the 2's and the 6 should not appear), because that's actually a character, but since I do not know in advance if the numbers will be that big, I can't simply make it count that extra character;

The tool works as long as the numbers after the denomination fill all the columns. Depending from the type of accounting map used, I may know in advance if it's expected that a column is blank, and so, using the number of spaces separating the numbers, it fills the correct information. The problem is when the data has zeros as blanks. In this example worksheet, having 3 numbers is alright (they must be the Debit, the Credit, and depending from which is bigger, i know the right balance column - and yes, the row for the first example is completely random). But when I have two numbers is a huge problem because there are three possibilities: they're both credit (movement and balance), they're both debit (movement and balance), or the movements are equal and so the balance is zero. Does anyone have any idea on how this problem can be addressed?

Thank you all.

[shukla.ankur281190]

Hey Rui Farinha,

Perhaps this could help you.

Put
B1=Denomination
C1=#
D1=Debit Mov.
E1=Credit Mov.
F1=Debit Balance
G1=Credit Balance

Try

B2

Please Login or Register  to view this content.

Drag down above formula.

C2

Please Login or Register  to view this content.

Drag down above formula and towards last column Credit Balance.

Check the attached file.

Hope this will help you.

[Rui Farinha]

Thank you for your reply.

I've made some minor changes because as I said, we work with commas as the decimal separator, but with your help it was easier to understand how I could do that, and then make them numbers. Those formulas were pretty neat.

Do you have any idea about my second request? I'm not even sure if it's possible, because I can't find any rule (for some accounts it would be possible since their movements should only be debits or credits, but not on all of them, like the ones in the sample).

In any case, thanks again.

[shukla.ankur281190]

Okay Try

C2

Please Login or Register  to view this content.

Check the attached file.

[Rui Farinha]

Not the difference I was hoping.

Anyway, I appreciate your effort and I'm extremely grateful since the formulas are much more flexible than the ones I had before.

I'll mark it as solved.