text to column

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.

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

Originally Posted by 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 ","
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,

Formula:

=VALUE(LEFT(A1,4))

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

Formula:

=RIGHT(A1,LEN(A1)-4))

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

Thanks. I just want to reply that

replace (string,"(*)",",")

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

Originally Posted by NeedForExcel

Hi,

Try this -

Put this Code

Option Explicit

Function TextToColumns(TextValue As Variant) As String
    Dim RegExp As Object
    
    Set RegExp = CreateObject("vbscript.regexp")
    
    With RegExp
        .Global = True
        .ignorecase = True
        .Pattern = "\([^)]*\)"
        TextToColumns = .Replace(TextValue, "|")
    End With

End Function

Then use this formula -

=IFERROR(LEFT(SUBSTITUTE(TextToColumns(Input!A1)," ",""),4)&"|"&RIGHT(SUBSTITUTE(TextToColumns(Input!A1)," ",""),LEN(SUBSTITUTE(TextToColumns(Input!A1)," ",""))-4),"")

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

Originally Posted by NeedForExcel

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

RangeObject.Replace What:="(*)", Replacement:=","

instead.