Hi,
You may also use the =TEXTBEFORE(A2,"@") formula.
Hope this helps.
Answer from Ashish Mathur on learn.microsoft.comHi,
You may also use the =TEXTBEFORE(A2,"@") formula.
Hope this helps.
Fill in what you want in the column next to the top two entries, and then select the next cell and use
Data / Flash fill
Excel will process all the entries based on the logic it derives from your examples.
Deleting specific parts of text in multiple cells
="[ "&TEXTJOIN(" ] [ ",,TRIM(TEXTBEFORE(TEXTAFTER(TRIM(TEXTSPLIT(A1,"[",,TRUE)),")"),"]")))&" ]"If A1 contains [ (1) Apple ] [ (2) Banana ]
It is converted to [ Apple ] [ Banana ]
More on reddit.comHow do I delete part of the text from every cell?
Is there a way to remove letters from a cell--while leaving the spaces intact?
u/chriscasemart - Your post was submitted successfully.
-
Once your problem is solved, reply to the answer(s) saying
Solution Verifiedto close the thread. -
Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
-
Include your Excel version and all other relevant information
Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
More on reddit.comRemove text leave numbers
If it's really everything to the left of the first space we encounter, try this:
=IFERROR(--LEFT(A2,FIND(" ",A2)-1),"")If we won't always / reliably have a space to trigger on, the following will pull everything to the left of the first letter we encounter:
=IFERROR(--TRIM(LEFT(A2,MIN(FIND({"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"},LOWER(A2)&"abcdefghijklmnopqrstuvwxyz"))-1)),"")Both formula options assume info in A (starting A2). Put in B2 and copy down as needed.
If you're not on the new array engine, submit the second formula with CSE (Ctrl Shift Enter) instead of just Enter like usual.
Hello. Is there a way that I can delete words or numbers in multiple cells simultaneously?
For example, [ (1) Apple ] [ (2) Banana ]
Could I delete (1) and (2) at the same time?
Thank you.
="[ "&TEXTJOIN(" ] [ ",,TRIM(TEXTBEFORE(TEXTAFTER(TRIM(TEXTSPLIT(A1,"[",,TRUE)),")"),"]")))&" ]"If A1 contains [ (1) Apple ] [ (2) Banana ]
It is converted to [ Apple ] [ Banana ]
One way is with Power Query.
You would need to add the unwanted words to the query file.
https://www.dropbox.com/s/30kma4s8uo1f4a7/PQueryRemoveCertainWords.xlsx?dl=1
Use SUBSTITUTE.
=SUBSTITUTE(A2,"Application: ","")
Actually, I think that the simplest way is to use the Find & Replace on the column concerned if you intend to delete the original later on.
Hit Ctrl+H.
Find
Application:("Application", colon, space) Replace by nothingIn the Find & Replace window, click on Options >> and make sure that the 'Within: ' is set to "Sheet" and that 'Match entire cell contents' is unchecked.
Otherwise, another function besides SUBSTITUTE() you can use is MID():
=MID(text, start, length)
In your case, you can use:
=MID(A2, 14, LEN(A2))
Which will take everything from the character position 14 (After all the characters in "Application: ") to the end of the text. LEN(A2) is actually larger than the text you want, but that doesn't matter, it means it'll take everything till the end.