place a character between spaces

[krazyhype19]

Hi,

I need to delimit the characters in a cell but text to column is not flexible to delimit all my data. I am figuring out this kind of solution in vba but don't know how to code. I want to place a "|" in every spaces in between a character. But the catch is after reaching the unique 4 character string, the next "|" will be before it reads a number.

Sample data:
1 ABCD EFGHI J. AALI 25,900.00 22,279.9567 577,050,878.75 25,100.0000 650,090,000.00 0.62 73,039,121.25

The output should be:
1 | ABCD EFGHI J. | AALI | 25,900.00 | 22,279.9567 | 577,050,878.75 | 25,100.0000 | 650,090,000.00 | 0.62 | 73,039,121.25

The data came from a pdf file and I just pasted in excel and now I need to arrange it in column.

Thanks!

[pjwhitfield]

Formula:

Please Login or Register  to view this content.

[krazyhype19]

Hi pj,

Thanks... how can I code this in vba? and how about the catch?

1 | ABCD EFGHI J. | AALI | 25,900.00
another one
2 | ABC EFGHIJ KLM | ABDC | 2,689.00

If I applied just substitute, the result will be like this:

1 | ABCD | EFGHI | J. | AALI | 25,900.00

2 | ABC | EFGHIJ | KLM | ABDC | 2,689.00

[apo]

Using the data you show in Post #3.... can you show how you want the data to look like..

[krazyhype19]

The data should look like this:
1 | ABCD EFGHI J. | AALI | 25,900.00
another one
2 | ABC EFGHIJ KLM | ABDC | 2,689.00

[apo]

But the catch is after reaching the unique 4 character string

What unique 4 character string?

the next "|" will be before it reads a number

The example data is not correlating to what you're saying..

[krazyhype19]

Or how will I place it a separate column. Original data is in #1.

[krazyhype19]

The unique char in my examples were: AALI and ABDC. That is before it reads a number. The before is this: 1 | ABCD and this 2 | ABC. Sorry, I mean after coz I am trying to start on the last char.

[krazyhype19]

Here's another example:
36 YAH DJSGFOEU HERO 80,700.00 3,303.7313 266,611,116.90 2,500.0000 201,750,000.00 0.19 64,861,116.90-
37 HFKJ UOWNR HRUM 60,600.00 5,382.8376 326,199,957.62 1,510.0000 91,506,000.00 0.09 234,693,957.62-
38 INDOWILL TAU W. ICBP 15,100.00 10,491.2713 158,418,196.58 11,075.0000 167,232,500.00 0.16 8,814,303.42

And it should be:
36|YAH DJSGFOEU|HERO|80,700.00|3,303.7313|266,611,116.90|2,500.0000|201,750,000.00|0.19|64,861,116.90-
37|HFKJ UOWNR|HRUM|60,600.00|5,382.8376|326,199,957.62|1,510.0000|91,506,000.00|0.09|234,693,957.62-
38|INDOWILL TAU W.|ICBP|15,100.00|10,491.2713|158,418,196.58|11,075.0000|167,232,500.00|0.16|8,814,303.42

[krazyhype19]

Here's another example:
36 YAH DJSGFOEU HERO 80,700.00 3,303.7313 266,611,116.90 2,500.0000 201,750,000.00 0.19 64,861,116.90-

37 HFKJ UOWNR HRUM 60,600.00 5,382.8376 326,199,957.62 1,510.0000 91,506,000.00 0.09 234,693,957.62-
38 INDOWILL TAU W. ICBP 15,100.00 10,491.2713 158,418,196.58 11,075.0000 167,232,500.00 0.16 8,814,303.42

And it should be:
36|YAH DJSGFOEU|HERO|80,700.00|3,303.7313|266,611,116.90|2,500.0000|201,750,000.00|0.19|64,861,116.90-
37|HFKJ UOWNR|HRUM|60,600.00|5,382.8376|326,199,957.62|1,510.0000|91,506,000.00|0.09|234,693,957.62-
38|INDOWILL TAU W.|ICBP|15,100.00|10,491.2713|158,418,196.58|11,075.0000|167,232,500.00|0.16|8,814,303.42

[pjwhitfield]

Thats not going to be straight forward..... is the 1st column always 2 digits?

Its possible to do something using SPLIT but its that 2nd column which causes the issue...

in your examples above.
36 & 37 have 2 "words" in col2
38 has 3, how is that decided? theres no discernable way to tell what should be, are you saying that....

1st column = 1st set of digits,
3rd column is the last "word" before numbers start
and by default therefore, 2nd column is the bit in between?

[krazyhype19]

I understand... that's why I want if possible to start from the last char until it reaches the 4 letter char. The process will be replacing the " " to "|" and when it read the char is a text it will offset 4 char then "|" again. It will stop there. Then maybe will start again on the first character and replace the first " " to "|".

[xlbiznes]

Try this code :

Please Login or Register  to view this content.

[krazyhype19]

The number on the left is just a the ID no. of the data. Second string is supposed to be a name so there's no limit how many characters it should have. Then the next 4 letter string which all example have is a code and I think it is always 4 letters.

[krazyhype19]

Hi xlbiznes,

It works but I've noticed that for string that has only 1 space in between it dont get the 4 letter char after.

For example:
3 ABCD FGHI JKLM 422,200.00 2,236.6854 944,328,586.98 2,705.0000 1,142,051,000.00 1.09 197,722,413.02

The output is this:
3|ABCD FGHI JKLM|422,200.00|2,236.6854|944,328,586.98|2,705.0000|1,142,051,000.00|1.09|197,722,413.02

Should be like this:
3|ABCD FGHI|JKLM|422,200.00|2,236.6854|944,328,586.98|2,705.0000|1,142,051,000.00|1.09|197,722,413.02

[xlbiznes]

try this update code :

Please Login or Register  to view this content.

[pjwhitfield]

Not an elegant solution but, if you want it as a Function.

Please Login or Register  to view this content.

[krazyhype19]

Thank you all guys!

I might try to enhance the code to make it work with a only 1 name string before the 4 letter code. just like 1 ABCDEFGH IGHY 89,000.00 to be 1|ABCDEFGH|IGY|89,000.00|...