text to column

[mrkhchan]

Hi All,

I have encounter for text to column. my data is as below

Range ("A1")=2008 76 294 (0) 69 683 (+7) 145 977 (+3) 43 021 (+18) 70 404 (+4) 113 425 (+9) 259 402 (+6)
Range("A2"=2009 76 240 (0) 63 053 (-10) 139 293 (-5) 38 626 (-10) 65 048 (-8) 103 673 (-9) 242 967 (-6)

the first word of the sentence is year and the separator is the bucket. I need to remove space in the data.
I want the table to be as below

2008 76294 69683 145977 43021 ...
2009 76240 63053 139293 38626 ...

I think the difficult to change the bucket to a separator.
Thanks in advance.

[quekbc]

RAA! I typed all the steps out, and when I was trying to insert pictures it failed, reload the site, and lost all my comments.

Anyhoo, you can do this in a few steps. I'm sure someone here will have a more efficient method but this is what I have on top of my head.

1) Do a Find and Replace All (Ctrl + H) and find "(*)", and replace it with ","
Step1.jpg
2) Then do a Text To Column: Delimited --> Comma
Step2.jpg
3) You should have in column A numbers that look like 2009 76 294.
4) In columns B onwards, you should have numbers without the year (e.g. 69 683)

To deal with column A
A1) Insert 2 columns after column A
A2) To extract year, in new column B,

Please Login or Register  to view this content.

A3) To extract remaining numbers, in new column C,

Please Login or Register  to view this content.

A4) Copy columns B and C and Paste Special --> Values

To deal with the spaces in the numbers
1) Do another Find and Replace All (Ctrl + H) and find " " and replace with ""
2) Note: the picture below you won't see any characters in the Find and Replace field, but know that the Find field will have 1 space in there, the Replace field will have nothing
Step3.jpg

EDIT: Or you can automate all of the steps above using VBA

[mrkhchan]

Thank you. I think I can write the macro using your concept.

RAA! I typed all the steps out, and when I was trying to insert pictures it failed, reload the site, and lost all my comments.

Anyhoo, you can do this in a few steps. I'm sure someone here will have a more efficient method but this is what I have on top of my head.

1) Do a Find and Replace All (Ctrl + H) and find "(*)", and replace it with ","
Attachment 402998
2) Then do a Text To Column: Delimited --> Comma
Attachment 402999
3) You should have in column A numbers that look like 2009 76 294.
4) In columns B onwards, you should have numbers without the year (e.g. 69 683)

To deal with column A
A1) Insert 2 columns after column A
A2) To extract year, in new column B,

Please Login or Register  to view this content.

A3) To extract remaining numbers, in new column C,

Please Login or Register  to view this content.

A4) Copy columns B and C and Paste Special --> Values

To deal with the spaces in the numbers
1) Do another Find and Replace All (Ctrl + H) and find " " and replace with ""
2) Note: the picture below you won't see any characters in the Find and Replace field, but know that the Find field will have 1 space in there, the Replace field will have nothing
Attachment 403000

EDIT: Or you can automate all of the steps above using VBA

[NeedForExcel]

Hi,

Try this -

Put this Code

Option Explicit

Please Login or Register  to view this content.

Then use this formula -

Please Login or Register  to view this content.

Check the attached file. Sheet named Input has the Input Text Strings and sheet named Output has Output text strings with | as delimiter..

You can then use Text to Columns using | as delimiter..

Hope it helps..

Deep

[mrkhchan]

Thanks. I just want to reply that

Please Login or Register  to view this content.

is not effective in vba. and Luckily read your reply.

Hi,

Try this -

Put this Code

Option Explicit

Please Login or Register  to view this content.

Then use this formula -

Please Login or Register  to view this content.

Check the attached file. Sheet named Input has the Input Text Strings and sheet named Output has Output text strings with | as delimiter..

You can then use Text to Columns using | as delimiter..

Hope it helps..

Deep

[NeedForExcel]

I am not sure what you mean?

So did my solution not help you with what you wanted?

[quekbc]

I am not sure what you mean?

So did my solution not help you with what you wanted?

I think what he means is that he wasn't able to do the replace in VBA effectively and is now trying your method.

mrkhchan: I like how you are taking in solutions and trying to adjust it yourself and I applaud you for that. I think it is the best way to learn. If it is of any help, the Replace function you are referring to is the VBA Replace function. You can utilise the Find & Replace tool in Excel (Ctrl F or Ctrl H) by using

Please Login or Register  to view this content.

instead.