extract Numbers into strings

[Maurizio]

Hi all,

with a string so:

27.990,000 0,00 0,00
MILANO 27.990,000 34,72 850,00
...

with vba how can I get this into 4 cells:

(empty)|27.990,000|0,00|0,00
MILANO | 27.990,000 | 34,72 | 850,00


Tia.

 

[Peter_SSs]

Assuming the original strings are in column A staring at row 2, try this.

Sub FourColumns()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=IF(ISNUMBER(LEFT(A2,1)+0),"" "","""") & A2"
    .Value = .Value
    .TextToColumns DataType:=xlDelimited, Space:=True, Other:=False
  End With
End Sub

 

[Maurizio]

Assuming the original strings are in column A staring at row 2, try this.

Sub FourColumns()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=IF(ISNUMBER(LEFT(A2,1)+0),"" "","""") & A2"
    .Value = .Value
    .TextToColumns DataType:=xlDelimited, Space:=True, Other:=False
  End With
End Sub

Many tnks

 

[Maurizio]

....and with:

38 CROI-LOGIS
B/152 RIOV LOGI SRL
B/56 SCARRONI

IN 2 COLUMNS ?

38 | CROI-LOGIS
B/152 | RIOV LOGI SRL
B/56 | SCARRONI

Tia.

 

[Peter_SSs]

Many tnks

You're welcome.

...and with:

38 CROI-LOGIS
B/152 RIOV LOGI SRL
B/56 SCARRONI

IN 2 COLUMNS ?

Does this do what you want?

Sub TwoColumns()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate("substitute(" & .Offset(, -1).Address & ","" "","";"",1)")
    .TextToColumns DataType:=xlDelimited, Semicolon:=True, Space:=False, Other:=False
  End With
End Sub

BTW, suggest that you update your Account details to show us version information as it may affect the most appropriate suggestion.

.. and also give us sample data in a better format? .. XL2BB

 

[Maurizio]

Sub TwoColumns() With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row) .Value = Evaluate("substitute(" & .Offset(, -1).Address & ","" "","";"",1)") .TextToColumns DataType:=xlDelimited, Semicolon:=True, Space:=False, Other:=False End With

... sorry I get:

38​	CROI-LOGIS
38​	CROI-LOGIS
38​	CROI-LOGIS

and not

38​	CROI-LOGIS
B/152	RIOV LOGI SRL
B/56	SCARRONI

 

[Peter_SSs]

... sorry I get:

Probably related to your excel version which is why suggested ...

... that you update your Account details to show us version information as it may affect the most appropriate suggestion.