How to condition formula on other cell content

Hi J

Here is the formula that would count the number of ; + 1 in a cell, which if all the persons were separated by a ; would work

(Pleas Note, you will need to replace A1 with the appropriate cell reference)

=LEN(A1)-LEN(SUBSTITUTE(A1,";","")) + 1

However from your screenshot, sometimes a ; is used and other times a , is used to separate the persons names, and in that case, this is the formula you need

=LEN(A1)-LEN(SUBSTITUTE(A1,";","")) + LEN(A1)-LEN(SUBSTITUTE(A1,",","")) + 1

Individual users can change the link for the app's UI using the link that appears on the lower left of the My Sways page, but this does not change the language of the content you have typed in. Sway does not have a built in translator so it will always
display in the language you entered. If you want to translate the content, the viewers will have to use a browser feature or add-on after opening it. One thing you can do to help with this translation is to indicate the language that your Sway is in using
the Settings pane found on the ... menu, but again, this won't do the actual translation.

Let us know how this goes and if you have any other questions.

Jason

Hi, thanks for your reply.
The Version is MS office 365, Excel 2208.
I know how to insert graphics in XLS, but if one photo is in a cell, there is no way to simply get that image into another cell just by typing "=A2".
Years ago that was easy to do, like that.
Now, e.g. doing a list of products with photos, it takes hours to use the "work around" getting images to the define cell from another table.
My question: why had that been changed and for what reason?
I thought a cell content is a cell content, either text or images, I see no difference handling the "content".
Honestly I miss that "old" functionality since years, and looking in YouTube you see lots of videos helping to get that simple job done - in 10-15 minutes.... Got it ?
BR
Axel

Lies bitte diesen Artikel:
<br />Formula - How to pull data from another sheet without getting mixed up - Microsoft Community
<br />Andreas.
<br />

Nee, das geht so nicht. A) muss jedes " verdoppelt werden, weil " ist in VBA ein reserviertes Zeichen und B) musst Du FormulaLocal statt der FormulaR1C1Local nehmen.

Und mit ActiveCell.Offset(0, 10) würde ich niemals arbeiten, weil dann landet die Formel je nach aktueller Zelle irgendwo.

Schau Dir das Beispiel unten mal an und versuche es nach dem Schema nachzubauen.

Andreas.

Sub Example_HowToCreateAFormulaWithVBA()

 'Copy the formula from Excel as is

 ' =INDEX(C1:C2,MATCH($A$1,B1:B2,0))&"Whatever"

 'double each " sign inside:

 ' =INDEX(C1:C2,MATCH($A$1,B1:B2,0))&""Whatever""

 'surround the whole formula with " signs

 ' "=INDEX(C1:C2,MATCH($A$1,B1:B2,0))&""Whatever"""

 'Replace the cell references with a "placeholder" (any unique string that you like and doesn't exists elsewhere)

 ' "=INDEX(#C#,MATCH(#A#,#B#,0))&""Whatever"""

 'and assign to a string:

 Dim MyDefFormula As String

 MyDefFormula = "=INDEX(#C#,MATCH(#A#,#B#,0))&""Whatever"""

 

 'Now create the Range objects and refer to the real cells

 Dim a As Range, b As Range, c As Range

 Set a = Range("A1") 'A single cell

 Set b = Range("B2:B50") 'Multiple cells

 Set c = Range("C2", Range("C" & Rows.Count).End(xlUp)) 'From C2 to last used cell in column C

 'Note:

 ' You can refer to cells in an other sheet (note the dot in front of Range!):

 'With Worksheets("That")

 ' Set C = .Range("C2", .Range("C" & Rows.Count).End(xlUp))

 'End With

 ' Or other already opened files:

 'With Workbooks("This.xlsx").Worksheets("That")

 ' Set C = .Range("C2", .Range("C" & Rows.Count).End(xlUp))

 'End With

 

 'Make a copy of your default formula into a work string

 Dim MyFormula As String

 MyFormula = MyDefFormula

 'Replace the placeholders with the cell references

 MyFormula = Replace(MyFormula, "#A#", a.Address)

 'Note:

 ' The first arguments of Range.Address controls where the $ appears in the formula

 MyFormula = Replace(MyFormula, "#B#", b.Address(0, 0))

 'Note:

 ' The part 'External:=True' is necessary if you refer to other sheets or files

 ' But it works also if the reference is in the active sheet

 MyFormula = Replace(MyFormula, "#C#", c.Address(External:=True))

 

 'Write the formula into the cell

 Range("A2").Formula = MyFormula

 

 'Note:

 ' If your locale language is not English you can use the FormulaLocal property instead

 ' But in this case the code works only on PCs with your locale settings

 'Range("A2").FormulaLocal = MyFormula

 

 'Note:

 ' If you want to create array formulas use the FormulaArray property

 ' But in this case use must use English formulas, a FormulaArrayLocale doesn't exists

 'Range("A2:A10").FormulaArray = MyFormula

End Sub