Help with editing excel sheet data

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

What is the difference bitween those 2?
Ans is there any way to move and not copy the number?

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.

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

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.

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).

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.

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.

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.

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)?

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"

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.

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

Select allOpen in new window

Now it doesn't work at all, says: #NAME?

Please refer to the attached.
GetNumber.xlsm

It does the work now but it copies it instead of moving it like it did before.

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.

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.

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.

sktneer:
A potential follow-up question...

[ https://www.experts-exchange.com/questions/28709755/Help-with-editing-cell-with-data-in-excel-sheet.html ]

@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.

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! :)