Edit within a cell to select parts

[Guest]

I have a spreadsheet of order numbers/names in one cell like this
20070001 Mickey Mouse
20070003 Daffy Duck
2006001222 Mini Mouse
200500345 Pluto

In VB, is there a way to select out just the number, cut it out, and paste
in different cell? Just have a few hundred to do and parsing the cell by
space requires a recombine of all the names. Thanks.

 

[Chip Pearson]

Is it the case that the number portion will contain zero spaces and will
always end with a space? If so, use something like

Sub AAA()
Dim Pos As Integer
Dim S As String
S = Range("A1").Text
Pos = InStr(1, S, Chr(32), vbBinaryCompare)
If Pos Then
Debug.Print Left(S, Pos - 1)
Range("B1").Value = Left(S, Pos - 1)
Else
Debug.Print "no space found"
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[john.galt.online]

I have a spreadsheet of order numbers/names in one cell like this20070001MickeyMouse
20070003 Daffy Duck
2006001222 MiniMouse
200500345 Pluto

In VB, is there a way to select out just the number, cut it out, and paste
in different cell? Just have a few hundred to do and parsing the cell by
space requires a recombine of all the names. Thanks.

It is easier to do if the number has a fixed number of digits. Then
you can use the left/right function combined with the value function
to grab the number. If you are uncertain how to do that, drop me a
note and I'll reply with a more detailed explanation.

If, however, the magnitude of the number is uncertain, but you know
for sure that there is a space between the number and the text, then
you can use the find function (looking for the space) in conjunction
with left and value to get the job done.

Let me know which case you are dealing with, and i'll drop in a more
detailed response.

Did you mention VB? For this simple thing, you won't need to use
VB...just Excel formulas

 

[Bernie Deitrick]

Use two formulas, one to get the number, and the other to get the name. For a value in cell A2, use

=LEFT(A2,FIND(" ",A2)-1)
=MID(A2,FIND(" ",A2)+1,LEN(A2))

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie...worked great...coupled with the automated For...Next statment and
runs great. Thanks.

 

[Bernie Deitrick]

You're welcome, but you don't need a For...Next structure:

Sub Macro1()
Dim myRow As Long
myRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2:B" & myRow).FormulaR1C1 = _
"=LEFT(RC[-1],FIND("" "",RC[-1])-1)"
Range("C2:C" & myRow).FormulaR1C1 = _
"=MID(RC[-2],FIND("" "",RC[-2])+1,LEN(RC[-2]))"
End Sub

HTH,
Bernie
MS Excel MVP