Excel Magic Trick 1002: Replace '11' With '13', But Only After Third Dash

Показать описание

See a formula that will replace "11" With "13", but only after the third dash. See the functions: SUBSTITUTE, SEARCH, MID, IF, REPLACE.

Рекомендации по теме

Комментарии

I added your video link to the show more area and also as an annotation in the video! Very nice formula:

excelisfun

Glad the trick helped! But it was not something that I invented, it was something I saw at the Mr Excel Message Board! Just passing it along... play it forward!

excelisfun

Wow! very clever on that # character. That's a real creativity in there. You made everybody smarter. Thanks a lot for that Mike... :-)

anabellepiramide

Awesome videos Mike 📹. You are an excel artist 🎨

nadermounir

This formula it's simply beautyfull !

ExcelStrategy

I am not sure how to download the video.

excelisfun

you could have done substitute to change the 3rd dash to a pound then substitute the pound and J1 to dash and J2.

francoistrempe

I need your help I hanve some numbers separated by "|" pipe sign I wanted to extract them in different cells.

bhaveshsuchak

Dear sir,

how to download excellsfun video in one time,

kindly advise.

regards

harish

harishnebhwani

REPLACE(i5, SEARCH(-, i5, 1+SEARCH(-, i5, 1+SEARCH(-, i5)))+1, 2, 13)

Excel

data example :- "11345| adhdhhgd ** 53547| dhfkgj ** 54646| dhhifhkgsjf ** 37578|" this may look like i tried text to columns but its not working i want only numbers in different cells

bhaveshsuchak

Excel Magic Trick 1002: Replace '11' With '13', But Only After Third Dash

Excel Magic Trick 1002: Replace '11' With '13', But Only After Third Dash

Excel Magic Trick 1002 Replace 11 With 13, But Only After Third Dash (nowtelugu.com)

Excel Magic Trick 1001: Lookup Biggest Number After Partial Text Lookup, Many Duplicates

Excel Magic Trick 1004: Conditionally Format Row With nth Duplicate, Column Sorted or Not Sorted

Excel Magic Trick 1000: Lookup Last Partial Text Item using SEARCH and LOOKUP functions

Excel Magic Trick 1025: Remove Asterisk From All Text Items In Column: SUBSTITUTE & TRIM

Excel Magic Trick 1011: Conditionally Format Row Above Matched Text Criteria 'Subtotal'

Excel Magic Trick 1003: Lookup nth Duplicate When Column With Lookup Value Is Sorted

Substitute, Replace, and Find Functions in Excel

Excel Magic Trick 1033 Re do Duel 134 Join Product and Sub Product Text Using PivotTable

Excel Magic Trick 808: Copy Sheet and link Only Cells With Raw Data Go To Special Select Constants

Excel Magic Trick 1415: Power Query Lookup and Return Multiple Items And Join Them In Single Cell

Advanced Find and Replace In MS Excel | Change format | Change cell reference | Use of wildcards

Excel Magic Trick 1018 CEILING Function To Round Up To The Nearest $1000 Increment (nowtelugu.com)

Excel Magic Trick 989: Conditional Formatting For Values above 90% Percentile Mark

Excel Magic Trick 1006: Insurance Calculation: MMULT Function to add from rows in a 2-Way Array

Excel Magic Trick 1049 Copy Page Setup From One Sheet To Another (nowtelugu.com)

Excel Magic Trick 1422: Power Query M Code: Join all Items From Invoice into One Cell

Excel Magic Trick 1042 Array Functions Housed In Other Functions Usually Don't Need Ctrl Shift ...

Excel Magic Trick 1012: Automatically Add All Subtotals In A Column (6 Methods)

Excel Magic Trick 1221: Dynamic Chart to Plot Daily Changes in Meter Readings

Excel Magic Trick 1315: Create A Unique Sorted List with Power Query

Using the Replace and Find Functions in Excel to Modify Strings

Video Tutorial Examen Excel Junior 1002