merge cells

[john_cash]

Hi to all,
this formula in cell contains "AAAA-" merge cell B3:B4

=IF(LEFT(B4,5)="AAAA-",B3&SUBSTITUTE(B4,"AAAA-",""),"")

i can't edit for cells without "AAAA-"
see attached example
john

[Estevaoba]

Try in C3 and copy down:

Please Login or Register  to view this content.

Buona fortuna!

[Bo_Ry]

Maybe try

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

[john_cash]

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

Thanks also a estevaoba
yuor formula work well
A greeting
john

[john_cash]

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

[oeldere]

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.

[Bo_Ry]

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

[john_cash]

Hi bo_by
I'm sorry but I made the wrong change
wrap text must be:

now your new change is
12345612345678
ASD ASD ASD

it must be
123456
12345678 ASD ASD ASD

[Richard Buttrey]

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

Please Login or Register  to view this content.

[Bo_Ry]

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

[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

[oeldere]

@john_cash

you did not reply on my solution.

[Richard Buttrey]

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.

[john_cash]

Thanks you richard buttrey

[john_cash]

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]

Hi oeldere
i saw your formula now but the result must be in a cell like formulas bo_ry and richard buttrey

[oeldere]

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

[john_cash]

oeldere, I don't understand what you mean

[oeldere]

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

[john_cash]

sorry oeldere but I don't understand how to use your formulas

[oeldere]

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.

[john_cash]

Oldere, it's incorrect.
In the attached example

F3:F4 they must be merge
F6:F7 they must be merge
F8:F9 they must be merge
F11:F12 they must be merge

F5 and F10 it must remain single

[Richard Buttrey]

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]

For richard buttrey:

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

[john_cash]

I badly explained myself?

[oeldere]

@john_cash

I badly explained myself?

You did not add to whom you are replying.

[Richard Buttrey]

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

[john_cash]

Hi richard example in post #24