Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.
Help with editing cell with data in excel sheet
Hi,
I have excel sheet that has a column with words + number, for Example: "Test 1234" or "Test1234" or "Test Test 1234",
I need VBA code which will split the string into columns,
The VBA Code will search the whole column and move the number to the cell next to it.
For example if cell A1 is: "Test 1234" and cell A2 is: "Test 4321" and cells B1 and B2 are empty,
Than this VBA Code will move from A1 "1234" to the B1 cell and from B1 it will move the "4321" to B2,
This code needs to ignore numbers that are smaller than 5 digits and cells with no number at all.
I have excel sheet that has a column with words + number, for Example: "Test 1234" or "Test1234" or "Test Test 1234",
I need VBA code which will split the string into columns,
The VBA Code will search the whole column and move the number to the cell next to it.
For example if cell A1 is: "Test 1234" and cell A2 is: "Test 4321" and cells B1 and B2 are empty,
Than this VBA Code will move from A1 "1234" to the B1 cell and from B1 it will move the "4321" to B2,
This code needs to ignore numbers that are smaller than 5 digits and cells with no number at all.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Place this code in a standard module and see if it does what you want
Option Explicit
Sub SplitEntries()
Dim rRng As Range, rCl As Range
Dim iX As Integer
Dim sNum As String
Set rRng = ActiveSheet.Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each rCl In rRng.Cells
sNum = ""
With rCl
For iX = 1 To Len(.Value)
'If the character is a number, add it to the sNum variable.
If IsNumeric(Mid(.Value, iX, 1)) Then sNum = sNum & Mid(.Value, iX, 1)
Next iX
If Len(sNum) >= 5 Then
.Offset(, 1) = sNum
.Value = Left(.Value, Len(.Value) - Len(sNum))
End If
End With
Next rCl
End Sub
How does this request differ from your previous question's requirements?
[ http://www.experts-exchange.com/questions/28707890/Help-with-editing-excel-sheet-data.html ]
[ http://www.experts-exchange.com/questions/28707890/Help-with-editing-excel-sheet-data.html ]
My previous question's requirements are not for editing, in this question I wrote that I am interesting in editing the cell content.
I was told that editing can be done with VBA code and that's what I am asking in here.
The reason I am not uploading a sample is because it's a very big excel file and it contains sensitive details of my work.
I was told that editing can be done with VBA code and that's what I am asking in here.
The reason I am not uploading a sample is because it's a very big excel file and it contains sensitive details of my work.
No need to upload your original workbook here.
You may create a dummy workbook with some dummy data (as you showed in your first post) and show the desired output. That would help the solution provider to know what exactly you are trying to achieve.
You dummy workbook may contain two sheets "Before" and "After" where you may show what you have originally in Before Sheet and the desired output in the After Sheet.
You may create a dummy workbook with some dummy data (as you showed in your first post) and show the desired output. That would help the solution provider to know what exactly you are trying to achieve.
You dummy workbook may contain two sheets "Before" and "After" where you may show what you have originally in Before Sheet and the desired output in the After Sheet.
Please find the attached workbook and on Test Sheet click the button "Click Here" to run the code and see if you get the desired output.
ExtractNumber.xlsm
ExtractNumber.xlsm
Premium Content
You need an Expert Office subscription to comment.Start Free Trial