merge cells

Maybe try

=IF(ISTEXT(B4),B3&MID(B4,6,20),IF(ISTEXT(B3),"",B3))

Hi bo_ry thank you, it's ok.
A greeting
john

Thanks also a estevaoba
yuor formula work well
A greeting
john

Hi bo_ry.
thank you again
Could the second merged cell be wrap text?

now is
12345612345678 ASD ASD ASD

it must be
12345612345678
ASD ASD ASD

john

If you would show the data for presentation reason, i would not break the line in 2 parts, but use this one instead.

The reason for this is that I expect problems, using the result of that formula in the future.


G3 =IF(AND(ISNUMBER(B3),ISTEXT(B4)),1,IF(AND(ISTEXT(B3),ISNUMBER(B4)),2,IF(AND(ISTEXT(B3),ISBLANK(B4)),4,3)))

F3 =IF(G3=3,B3,IF(OR(G3=2,G3=4),RIGHT(C2,LEN(C2)-SEARCH(" ",C2,1)-1),IF(G3=1,LEFT(C3,SEARCH(" ",C3,1)-1),"")))

See the attached file.

Please try at C3
=IF(ISTEXT(B4),SUBSTITUTE(TRIM(B3&MID(B4,6,50))," ",CHAR(10),1),IF(ISTEXT(B3),"",B3))

I've arrived here a bit late, but maybe on row 3

Formula:

Please Login or Register  to view this content.

Try at C3
=IF(ISTEXT(B4),TRIM(B3&CHAR(10)&MID(B4,6,50)),IF(ISTEXT(B3),"",B3))

@john_cash

you did not reply on my solution.

Originally Posted by john_cash

Hi bo_ry the new modified work well, thank you.
A greeting.
john

For richard buttrey the your formula work well but wrap text doesn't work.

it must be so
123456
A2345678 ASD ASD ASD

your formula is now
123456A2345678 ASD ASD ASD

Re the Wrap Text. It will work if you select those columns and choose the Wrap Text option on Excel's Ribbon menu to format the cells.

Because in cell B5 if it's just number it works, but if it's text or text and number doesn't it work anymore?

@john_cash

The reason for this is that I expect problems, using the result of that formula in the future.

The result will not be used in another formula?

The result of the "wrapped" formula.

If that result is in e.g. C3.

Will you use the result of C3 in a formula in e.g. F2.

In that case I expect problems using char(10).

cell G3 =IF(AND(ISNUMBER(B3),ISTEXT(B4)),1,IF(AND(ISTEXT(B3),ISNUMBER(B4)),2,IF(AND(ISTEXT(B3),ISBLANK(B4)),4,3))) and drag down.

cell F3 =IF(G3=3,B3,IF(OR(G3=2,G3=4),RIGHT(C2,LEN(C2)-SEARCH(" ",C2,1)-1),IF(G3=1,LEFT(C3,SEARCH(" ",C3,1)-1),""))) and drag down.

Originally Posted by john_cash

Hi bo_ry the new modified work well, thank you.
A greeting.
john

For richard buttrey the your formula work well but wrap text doesn't work.

it must be so
123456
A2345678 ASD ASD ASD

your formula is now
123456A2345678 ASD ASD ASD

I presume after you've applied the text wrap formatting to the columns that it now gives you what you want. If not upload the workbook so that we can check what you've entered.

@john_cash

I badly explained myself?

You did not add to whom you are replying.

Originally Posted by john_cash

For richard buttrey:

example in B2 if 123456 work, if AAA 123456 not work

Hi,

Rather than just saying it doesn't work you need to make sure that the workbook you upload contains all permutations of data, and by permutations that means including different succeeding cells where these are relevant to what you see in an earlier cell.

In other words we need you to manually add what you expect to see. For instance you are indicating C5 is wrong, but don't show what you do expect to see and importantly WHY.

Please create a workbook with various permtuations with manually added results and upload the workbook