# extract Numbers into strings

#### Maurizio

##### Well-known Member
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.

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### Peter_SSs

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

VBA Code:
``````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

##### Well-known Member
Assuming the original strings are in column A staring at row 2, try this.

VBA Code:
``````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

##### Well-known Member
....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

##### MrExcel MVP, Moderator
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?

VBA Code:
``````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

##### Well-known Member
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

##### MrExcel MVP, Moderator
... 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.