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

See how to remove asterisk from all text items in column using the: SUBSTITUTE & TRIM functions.
See how to copy a formula down a column when the Mouse Double Click Method does not work using the Name Box

[excelisfun]

Yes, cool! The tilde ~ tells Excel to not treat the wild card as a wild card but as a character. It works in some functions like MATCH and COUNTIF. That is the type of advanced formula detail that is in the book I just published: Ctrl + Shift + Enter; Mastering excel Array Formulas.

[excelisfun]

Yes, the name box trick is a useful one!

[scoutcool]

Hold shift & enter to highlight whole column is awesome! I'll have to futz around the mac to see what the alternative is, but that's awesome!

[krn]

Thanks Mike. Never saw the name box trick before. Cool.

[akassebaum]

Is there a way to do this with a large set of IP Addresses of variable length and then add something at the end of all entries? For example

192.168.45.1
192.168.45.2
192.168.45.3
10.10.40.1
10.10.40.2

I would like the output to look like this:
10.10.40.0/24
192.168.45.0/24

This is to allow me to find all the Class C subnets that I can filter through. Amazing videos!

[mysimonhughes]

Hi Mike, I had an interesting issue with asterix. Some dimensions in a table were shown as 500*500*250 and others as 500x500x250 and I wanted to replace the asterix * with x for uniformity but the find/replace just deleted everything with the asterix as it saw the * as a wildcard. MARK858 on MREXCEL forum solved this by telling me to find ~* and replace with x. Perfect.

[md.abubakerhabib]

Substitute Copy function doesn't work in my excel sheet. When I try to copy it's happen the same thing means It works only single data.

[excelisfun]

Post question to THE best Excel question site:

mrexcel {dot] com/forum