extract Numbers into strings

Maurizio

Well-known Member
Joined
Oct 15, 2002
Messages
652
Office Version
2007
Platform
Windows
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.
 

Some videos you may like

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
Joined
May 28, 2005
Messages
43,532
Office Version
365
Platform
Windows
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
Joined
Oct 15, 2002
Messages
652
Office Version
2007
Platform
Windows
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
Joined
Oct 15, 2002
Messages
652
Office Version
2007
Platform
Windows
....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
Joined
May 28, 2005
Messages
43,532
Office Version
365
Platform
Windows
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.
1582063280902.png


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

Maurizio

Well-known Member
Joined
Oct 15, 2002
Messages
652
Office Version
2007
Platform
Windows
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
 

Forum statistics

Threads
1,089,644
Messages
5,409,497
Members
403,266
Latest member
HMR120

This Week's Hot Topics

Top