Formula help with cells containing letters

K

Kiwi den

Board Regular
Joined
Feb 17, 2014
Messages
153
Office Version
  1. 365
Platform
  1. Windows
Hi all.
I am trying to get around a problem, where i want a formula to appear in a cell, The issue is the "text" will either be UJ or FWM, note the text will be followed by a series of numbers, the only constant will be the UJ or FWM
so far i have
(cell m2) =IF(OR(B2="",K2<>0),"",now for the unknown, if it can be done?) If formula contains required text, then to multiply by cell L2

Any help appreciated
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
You can give some examples, use xl2bb tool to put a minisheet

What I want to see is what you have in B2, K2, L2 and any other cell that is involved.

So with 3 or 4 examples that you give, you should put in M2 the result you want. Don't put the formula, we need to see the result you want.
 
Upvote 0
DanteAmor said:
You can give some examples, use xl2bb tool to put a minisheet

What I want to see is what you have in B2, K2, L2 and any other cell that is involved.

So with 3 or 4 examples that you give, you should put in M2 the result you want. Don't put the formula, we need to see the result you want.
Click to expand...
Ok sorry as follows

RANGE=rs:22|cs:14|w:mr excel sample.xlsx|cls:xl2bb-210|s:Sheet1|tw:832][XR][XH][/XH][XH=w:52]A[/XH][XH=w:69]B[/XH][XH=w:89]C[/XH][XH=w:52]D[/XH][XH=w:52]E[/XH][XH=w:52]F[/XH][XH=w:52]G[/XH][XH=w:52]H[/XH][XH=w:57]I[/XH][XH=w:52]J[/XH][XH=w:71]K[/XH][XH=w:52]L[/XH][XH=w:74]M[/XH][XH=w:52]N[/XH][/XR][XR][XH]1[/XH][XD=h:c|fw:b|ch:15|nf:dd/mm]DATE[/XD][XD=h:c|fw:b]CODE[/XD][XD=h:c|fw:b]CLIENT[/XD][XD=h:c|fw:b]LOCATION[/XD][XD=h:c|fw:b]ASILE[/XD][XD=h:c|fw:b]WEIGHT[/XD][XD=h:c|fw:b|nf:\005B$$-1409\005D#,##0.00]VALUE[/XD][XD=h:c|fw:b|nf:\005B$$-1409\005D#,##0.00]FREIGHT[/XD][XD=h:c|fw:b|nf:\005B$$-1409\005D#,##0.00]SALESMAN[/XD][XD=h:c|fw:b|nf:\005B$$-1409\005D#,##0.00]BUDGET[/XD][XD=h:c|fw:b|nf:\005B$$-1409\005D#,##0.00]SURCHARGE[/XD][XD=h:c|fw:b|nf:0%]DIFF[/XD][XD=fw:b][/XD][XD=fw:b][/XD][/XR][XR][XH]2[/XH][XD=ch:14.25|tx:45635|nf:dd/mm]09/12[/XD][XD=h:c]6UJ0003706[/XD][XD=h:l]Able Access[/XD][XD=h:c]Field[/XD][XD=h:l]Blue[/XD][XD=h:c]21.4[/XD][XD=h:c|tx:490|nf:\005B$$-1409\005D#,##0.00]$490.00[/XD][XD=h:c|tx:23.46|nf:\005B$$-1409\005D#,##0.00]$23.46[/XD][XD=h:l|nf:\005B$$-1409\005D#,##0.00]Steve[/XD][XD=tx:550|nf:\005B$$-1409\005D#,##0.00]$550.00[/XD][XD=h:c|tx:15|nf:\005B$$-1409\005D#,##0.00]$15.00[/XD][XD=h:c|tx:0.15|nf:0%]15%[/XD][XD=cls:fx|nf:\005B$$-1409\005D#,##0.00]$12.75[/XD][XD][/XD][/XR][XR][XH]3[/XH][XD=ch:14.25|tx:45635|nf:dd/mm]09/12[/XD][XD=h:c]FWM55467911[/XD][XD=h:l]Mainstream[/XD][XD=h:c]Akld[/XD][XD=h:l]Brown[/XD][XD=h:c]22.9[/XD][XD=h:c|tx:721.6|nf:\005B$$-1409\005D#,##0.00]$721.60[/XD][XD=h:c|tx:32.11|nf:\005B$$-1409\005D#,##0.00]$32.11[/XD][XD=h:l|nf:\005B$$-1409\005D#,##0.00]Kevin[/XD][XD=tx:822.36|nf:\005B$$-1409\005D#,##0.00]$822.36[/XD][XD=h:c|tx:28.8|nf:\005B$$-1409\005D#,##0.00]$28.80[/XD][XD=h:c|tx:0.2|nf:0%]20%[/XD][XD=cls:fx|nf:\005B$$-1409\005D#,##0.00]$23.04[/XD][XD][/XD][/XR][XR][XH]4[/XH][XD=ch:14.25|tx:45636|nf:dd/mm]10/12[/XD][XD=h:c]6UJ0003708[/XD][XD=h:l]Clear Air[/XD][XD=h:c]Wgtn[/XD][XD=h:l]Green[/XD][XD=h:c]7.3[/XD][XD=h:c|tx:951.8|nf:\005B$$-1409\005D#,##0.00]$951.80[/XD][XD=h:c|tx:11.72|nf:\005B$$-1409\005D#,##0.00]$11.72[/XD][XD=h:l|nf:\005B$$-1409\005D#,##0.00]Steve[/XD][XD=tx:1512.5|nf:\005B$$-1409\005D#,##0.00]$1,512.50[/XD][XD=h:c|tx:36.22|nf:\005B$$-1409\005D#,##0.00]$36.22[/XD][XD=h:c|tx:0.15|nf:0%]15%[/XD][XD=cls:fx|nf:\005B$$-1409\005D#,##0.00]$30.79[/XD][XD][/XD][/XR][XR][XH]5[/XH][XD=ch:14.25|tx:45636|nf:dd/mm]10/12[/XD][XD=h:c]FWM54126687[/XD][XD=h:l]Concrete Structure [/XD][XD=h:c]ROT[/XD][XD=h:l]Green[/XD][XD=h:c]0.9[/XD][XD=h:c|tx:12.1|nf:\005B$$-1409\005D#,##0.00]$12.10[/XD][XD=h:c|tx:8.01|nf:\005B$$-1409\005D#,##0.00]$8.01[/XD][XD=h:l|nf:\005B$$-1409\005D#,##0.00]Val[/XD][XD=tx:16.98|nf:\005B$$-1409\005D#,##0.00]$16.98[/XD][XD=h:c|tx:21.88|nf:\005B$$-1409\005D#,##0.00]$21.88[/XD][XD=h:c|tx:0.2|nf:0%]20%[/XD][XD=cls:fx|nf:\005B$$-1409\005D#,##0.00]$17.50[/XD][XD][/XD][/XR][XR][XH]6[/XH][XD=ch:14.25|tx:45637|nf:dd/mm]11/12[/XD][XD=h:c]6UJ0003710[/XD][XD=h:l]Hind Furnish[/XD][XD=h:c]Wairoa[/XD][XD=h:l]Yellow[/XD][XD=h:c]30[/XD][XD=h:c|tx:145.92|nf:\005B$$-1409\005D#,##0.00]$145.92[/XD][XD=h:c|tx:18.26|nf:\005B$$-1409\005D#,##0.00]$18.26[/XD][XD=h:l|nf:\005B$$-1409\005D#,##0.00]Kevin[/XD][XD=tx:243.49|nf:\005B$$-1409\005D#,##0.00]$243.49[/XD][XD=h:c|tx:38.1|nf:\005B$$-1409\005D#,##0.00]$38.10[/XD][XD=h:c|tx:0.15|nf:0%]15%[/XD][XD=cls:fx|nf:\005B$$-1409\005D#,##0.00]$32.39[/XD][XD][/XD][/XR][XR][XH]7[/XH][XD=ch:14.25|tx:45638|nf:dd/mm]12/12[/XD][XD=h:c]6UJ0003712[/XD][XD=h:l]Wettie Wetsuits[/XD][XD=h:c]Akld[/XD][XD=h:l]Red[/XD][XD=h:c]7.4[/XD][XD=h:c|tx:224|nf:\005B$$-1409\005D#,##0.00]$224.00[/XD][XD=h:c|tx:11.72|nf:\005B$$-1409\005D#,##0.00]$11.72[/XD][XD=h:l|nf:\005B$$-1409\005D#,##0.00]Val[/XD][XD=tx:411|nf:\005B$$-1409\005D#,##0.00]$411.00[/XD][XD=h:c|tx:4.07|nf:\005B$$-1409\005D#,##0.00]$4.07[/XD][XD=h:c|tx:0.15|nf:0%]15%[/XD][XD=cls:fx|nf:\005B$$-1409\005D#,##0.00]$3.46[/XD][XD][/XD][/XR][XR][XH]8[/XH][XD=ch:12.75][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]9[/XH][XD=ch:12.75][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]10[/XH][XD=ch:12.75][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]11[/XH][XD=ch:12.75][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]12[/XH][XD=ch:12.75][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]13[/XH][XD=ch:12.75][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]14[/XH][XD=ch:63.75][/XD][XD=h:l|fw:b|bc:FFFF00|cls:ww]CONSTANT: Either UJ or FWM[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD=h:l|fw:b|bc:FFFF00|cls:ww]formula req'd multiply "SURCHARGE" x "DIFF"[/XD][XD][/XD][/XR][XR][XH]15[/XH][XD=ch:12.75][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]16[/XH][XD=ch:12.75][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]17[/XH][XD=ch:12.75][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]18[/XH][XD=ch:12.75][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]19[/XH][XD=ch:12.75][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]20[/XH][XD=ch:12.75][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]21[/XH][XD=ch:12.75][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]22[/XH][XD=ch:12.75][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][/RANGE]
Cell Formulas
RangeFormula
M2:M7M2=K2-(K2*L2)
 
Upvote 0
To add a minisheet follow the instructions in this link: XL2BB


The minisheet should appear like this:
varios 18dic2024.xlsm
ABCDE
1BagueCageEleveursStam
2CH597Perez Renée1247
3PH12Jean Pierre254
4124Dupuy Francois369
5236Guillot Louis444
Classe A
Cell Formulas
RangeFormula
D2D2=D16

Note: this is just an example, we need to see your examples.
🤗
 
Upvote 0
DanteAmor said:
To add a minisheet follow the instructions in this link: XL2BB


The minisheet should appear like this:
varios 18dic2024.xlsm
ABCDE
1BagueCageEleveursStam
2CH597Perez Renée1247
3PH12Jean Pierre254
4124Dupuy Francois369
5236Guillot Louis444
Classe A
Cell Formulas
RangeFormula
D2D2=D16

Note: this is just an example, we need to see your examples.
🤗
Click to expand...
mr excel sample.xlsx
ABCDEFGHIJKLM
1DATECODECLIENTLOCATIONASILEWEIGHTVALUEFREIGHTSALESMANBUDGETSURCHARGEDIFF
209/126UJ0003706Able AccessFieldBlue21.4$490.00$23.46Steve$550.00$15.0015%$12.75
309/12FWM55467911MainstreamAkldBrown22.9$721.60$32.11Kevin$822.36$28.8020%$23.04
410/126UJ0003708Clear AirWgtnGreen7.3$951.80$11.72Steve$1,512.50$36.2215%$30.79
510/12FWM54126687Concrete Structure ROTGreen0.9$12.10$8.01Val$16.98$21.8820%$17.50
611/126UJ0003710Hind FurnishWairoaYellow30$145.92$18.26Kevin$243.49$38.1015%$32.39
712/126UJ0003712Wettie WetsuitsAkldRed7.4$224.00$11.72Val$411.00$4.0715%$3.46
8
9
10
11
12
13
14CONSTANT: Either UJ or FWMformula req'd multiply "SURCHARGE" x "DIFF"
15
16
Sheet1
Cell Formulas
RangeFormula
M2:M7M2=K2-(K2*L2)
 
Upvote 0
Is this what you need?

Libro1
ABCDEFGHIJKLM
1DATECODECLIENTLOCATIONASILEWEIGHTVALUEFREIGHTSALESMANBUDGETSURCHARGEDIFF
2456356UJ0003706Able AccessFieldBlue21.449023.46Steve550150.1512.75
345635FWM55467911MainstreamAkldBrown22.9721.632.11Kevin822.3628.80.223.04
4456366UJ0003708Clear AirWgtnGreen7.3951.811.72Steve1512.536.220.1530.787
545636FWM54126687Concrete StructureROTGreen0.912.18.01Val16.9821.880.217.504
6456375DD123456Hind FurnishWairoaYellow30145.9218.26Kevin243.4938.10.15 
7456386UJ0003712Wettie WetsuitsAkldRed7.422411.72Val4114.070.153.4595
8456386EX0003712Wettie WetsuitsAkldRed7.422411.72Val4114.070.15 
Hoja1
Cell Formulas
RangeFormula
M2:M8M2=IF(OR(ISNUMBER(SEARCH("UJ",B2)),ISNUMBER(SEARCH("FWM",B2))),K2-(K2*L2),"")


🧙‍♂️
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

D
  • Question Question
How to keep a tally of matching letters in separate cells
Replies
4
Views
666
Davenil
D
K
  • Question Question
If Formula
Replies
6
Views
196
Scott Huish
Scott Huish
D
  • Solved
AutoFill or Copy Formula to All Relevant Cells
Replies
5
Views
789
deZine
D
K
  • Solved
Excel VBA prompt for cells, then use in relative formula
Replies
2
Views
324
kitty111222
K
F
  • Solved
Help with error handling inside a for loop
Replies
3
Views
227
Firesword
F

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top