• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Condition formula

dettox

dettox

New Member
Hi to all, im new on this forum and look forward helping others if i can.

Im here for one thing today, i have a problem. In Excel i am trying to make a formula that inserts a line if there is 4 numbers in a cell.
Here is an example : Actual cell 285435421
What i want to achieve : 2854
3542
1
This is basicly what i want and i believe there is a formula for this kind of task. A condition formula where once the cell has more than 4 numbers it redirects the other numbers into the cell underneath and so on and so forth.

Best regards,
Dettox
 
Maybe the formula should start by If cell is bigger than 9999 then insert line and add the folowing numbers ?

Is this possible ?
 
If I'm understanding you correctly, the following three formulas should work:

Place your original number in Cell A1
A2: =IF(LEN(A1)>0,LEFT(A1,4),"")
A3: =IF(LEN(A1)>4,MID(A1,5,MIN(LEN(A1)-4,4)),"")
A4: =IF(LEN(A1)>8,RIGHT(A1,1),"")
 
Last edited:
Hi @dettox,
Good day and welcome to the forum :awesome:

Supposed your data is in A1, try this in B1 and copy down:

=MID(A$1,1+(ROW(A1)-1)*4,4)

Regards,
 
Well i tried this on Onedrive spreadsheet without great sucess on both formulas. The formula language is in french, i will have to wait until monday to try it on a real excel program 2013.

I will keep your informed, in the mean time try the formula on your end and if it works could you send me a downloadable spreadsheet with the example ?

Best regards,
 
Well, in that Onedrive screenshot, you appear to be in English, not French. You mentioned on Friday you were using a French version, so I offered a translation.

When I try Khalid's original formula (=MID(A$1,1+(ROW(A1)-1)*4,4)) in Onedrive, it seems to work fine. Note that you need to change the cell references as you copy this formula for each new data cell. For example, you need to change the A$1 to the correct data cell and make sure the Row(A1) is always Row(A1) in the first cell and increases to Row(A3) in the last of the three cells.

Given that, you might find copying my formulas in three line blocks would be easier as they should change cell references correctly.
 
Look onedrive doesn't work for me : http://prntscr.com/ee66bp
But i will try the formula in a real excel program tomorow.

Thanks for your help!

Here is my project, first there is the formula that takes the caracters and numbers and only keeps the numbers. And next i need them to be 4 numbers per cell going down.
 

Attachments

  • Excel folder.xlsx
    8.4 KB · Views: 2
Well, the actual data is a bit different than originally discussed. However, using the len, left, mid, or right text functions will easily get you 4 numbers per cell.

I hadn't used Excel in Onedrive before and don't think I'll play with it again. Life's too short already.
 
huh. I'm at a bit of a disadvantage since I don't have a French Excel, but it looks like it's accepting the STXT function fine. Is it objecting to identifying the numbers in A1 as text? If it gives an error, please provide the text as that will help.

Do a couple of things. First, replace the numbers with a random text string and see what happens.

If it likes that, then try:

=STXT(texte(A$1,"#"),1+(LIGNE(A1)-1)*4,4)

This all assumes nothing other than the Excel function name changes with language conversion.
 
Hi !

dettox, Mike, any local Excel version like a french one
can accept an english formula 'cause in fact
internally all rest natively in english !

The tip is to enter the formula via VBE Immediate window
aka Exécution in french version :
​
VBEnativeFormula.gif

​
 
You must log in or register to reply here.
Back
Top