Help with editing excel sheet data

Hi,

I have excel sheet that has a column with word + number, for Example: "Test 1234",
Is there any function that 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 function will move from A1 "1234" to the B1 cell and from B1 it will move the "4321" to B2.
Leon LimbaevAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

I have attached a workbook that has two array formulas to address your question.

The first (in cells [B1] & [B2]):
=LOOKUP(10^99,--MID(A1,MIN(IF((--ISNUMBER(--MID(A1,ROW($1:$25),1))=0)*ISNUMBER(--MID(A1,ROW($2:$26),1)),ROW($2:$26))),ROW($1:$25)))

The second (in cells [C1] & [C2]):
=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

(Please note: If entering either of these in your own workbook, as they are array formulas, instead of just using [Enter] to confirm entry into a cell, use [CTRL]+{SHIFT]+[ENTER]).

Credit to the contributors within the following threads:

[ http://www.mrexcel.com/forum/excel-questions/443983-extract-only-numbers-text-string.html ]
[ http://www.mrexcel.com/forum/excel-questions/362184-extracting-multiple-numbers-string.html ]
[ http://www.mrexcel.com/forum/excel-questions/440487-extract-text-number-column-help.html ]
Q-28707890.xls
Leon LimbaevAuthor Commented:
What is the difference bitween those 2?
Ans is there any way to move and not copy the number?
[ fanpages ]IT Services ConsultantCommented:
The two formulae tackle the same issue in different ways, but if you wanted to move the numeric digits, rather than copying the digits to column [ B ], do you mean you wish to update the values in column [A] whilst creating the entries in column [ B ]?

Are the existing values in column [A] of a fixed format?

That is,

Always 4 alphanumeric characters following by 4 numeric digits?  Or do the quantity of prefix characters, or the quantity of suffix characters, vary?

Are the two sets of characters/digits separated by a space character?

Depending on your data, there may well be a few options that could be used.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Roy CoxGroup Finance ManagerCommented:
You may be able to us Text to Columns. Select the column, open the Data Tab and click Text To Columns and follow the Wizard, see this
Leon LimbaevAuthor Commented:
The data I have is words and then a number, they are seperated only with a space, The number is up to 10 Characters,
Another solution I thought of without editing the A cell is to copy the words to the B cell and the number to C cell,
That way in the end of the proccess I can delete the A column.
[ fanpages ]IT Services ConsultantCommented:
If always separated by a single space character, then you can use the "Text to Columns" approach (as I was hinting at above, & as Roy_Cox posted a link to a page on another site that describes the process).
Leon LimbaevAuthor Commented:
I can't use "Text to Columns" option because some cells contain one word and some two,
I think a formula to copy the words from A cell to the B cell and the number to C cell will be the best solution here.
[ fanpages ]IT Services ConsultantCommented:
I have excel sheet that has a column with word + number, for Example: "Test 1234",
I can't use "Text to Columns" option because some cells contain one word and some two,

A definitive explanation of the format of your data would help, I think.

Perhaps you can provide some examples.
Leon LimbaevAuthor Commented:
The words are in Hebrew but its basicly Compnay name that can be one or two or three words and in some cell you have there words' plus a phone number,
I am looking to seperate this number from the company name and move it to a seperate column.
[ fanpages ]IT Services ConsultantCommented:
Is that your final answer; (initially one, then two, & now) three words, plus one number of up to 10 digits in length?

Do any of the telephone numbers have spaces between any of the digits, or are they all contiguous without any other non-numeric characters (spaces, hyphens, parentheses, or anything else)?
Leon LimbaevAuthor Commented:
Yep, That's my final answer,
The phone number are all contiguous,
The solution in that is given in the first answer works well except it won't move the number but copy it,
So I thought maybe to add to the formula a function that will also copy the text to a column near the column that it copied the number.

For example:

Input:
Cell A1 have: "test12345678" (There's no space because some of the cells do have the number sticked to the text, and the function in answer 1 works on those too)
Cell A2 have: "Test Test 1234567890"
Cell A3 have: "Test Test Test 123456789"

The output will be:
Cell B1 will have: "test" | Cell C1 will have: "12345678"
Cell B2 wiil have: "Test Test" | Cell C2 will have: "1234567890"
Cell B3 will have: "Test Test Test" | Cell C3 will have: "123456789"
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may use an User Defined Function function to achieve this....

On a standard module, place the following code and save your workbook as Macro-Enabled Workbook and you have your new function GetNumber to extract the number from a alphanumeric string.

Function GetNumber(str As String)
Dim i As Long
Dim num As String
If Len(str) > 0 Then
    For i = 1 To Len(str)
        If IsNumeric(Mid(str, i, 1)) Then
            If num = "" Then
                num = Mid(str, i, 1)
            Else
                num = num & Mid(str, i, 1)
            End If
        End If
    Next i
    GetNumber = num + 0
Else
    GetNumber = ""
End If
End Function

Open in new window


Then use this function as GetNumber(A1).

Refer to the attached workbook for details.
GetNumber.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leon LimbaevAuthor Commented:
Just found out that there are cells that doesn't have a number at all,
And also that there are cells that have numbers 2-4 chars that I don't want to move,
So the function is missing option to ignore when there is no number at all or a number of less than 5 chars.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Replace the previous Function with the following one.

Function GetNumber(str As String)
Dim i As Long
Dim num As String
If Len(str) > 0 Then
    For i = 1 To Len(str)
        If IsNumeric(Mid(str, i, 1)) Then
            If num = "" Then
                num = Mid(str, i, 1)
            Else
                num = num & Mid(str, i, 1)
            End If
        End If
    Next i
    If Len(num) >= 5 Then
        GetNumber = num + 0
    Else
        GetNumber = ""
    End If
Else
    GetNumber = ""
End If
End Function

Open in new window

Leon LimbaevAuthor Commented:
Now it doesn't work at all, says: #NAME?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please refer to the attached.
GetNumber.xlsm
Leon LimbaevAuthor Commented:
It does the work now but it copies it instead of moving it like it did before.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Is it working or not?
If not, you can upload the file back with some of your values and apply the function and let me know what is not working and what is the correct output you are expecting it to return.
Leon LimbaevAuthor Commented:
First of all thank you for all the help and for staying with me up to comment 19 :), I really appreciate it.

No need for me to upload my file because in the file you uploaded it does the same,
The only thing that isn't working is that it copies the number instead of moving it,
For example in you document A1 cell contains test12345678,
After the function runs should be left only the word test in A1and the number should move to A2.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I think that's a different question altogether.
The UDF I provided will extract the digits from your string as per your set criteria. An UDF cannot change the content of any cell on the sheet, so you will need another VBA code which you may run to split the string into columns.

It would be better if you open a new question. I will try to assist you in your new question.
[ fanpages ]IT Services ConsultantCommented:
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@fanpages

I am really not sure what OP is trying to achieve and at the same time he is not interested in uploading a sample file along with the desired output.
[ fanpages ]IT Services ConsultantCommented:
OK.  No problem.  I just thought you may like to continue as you have a grounding in the solution (as I read it).

You're right though, sometimes leaving similar questions to somebody else is better for your sanity! :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.