Hey everyone, I have a CSV file with phone numbers that I want to transform from this - (456) 456-2332 to 4564562332. Completely removing the parentheses & dashes. None of the stuff I've tried on the internet is working, any thoughts on how I could do this using a function or some other means besides manually changing them all?

like
Posting as :
works at
You are currently posting as works at

Man you guys are lazy, just help the guy out or don’t comment. =substitute(substitute(substitute(substitute(reference cell, “)”,””),”(“,””),”-“,””),” “,””)

Copy or drag down on all fields you want used and it will calculate the format you are looking for.

like

This is the best answer, the substitute formula is money especially when you just want to get rid of unwanted characters. If there are blank leading or trailing spaces OP you could also add:
=TRIM(CLEAN(*the working substitute formula*))

like

Few different options. You can always Find each character and replace with blank (not a space).

like

OP I think it would help if you tried the solution first and also look at all the options that Excel gives you before reacting. You can find and replace all from that menu.

like

Mid and right formulas.
Text to columns then recombine
Power query.

like

Okay so hear me out, I'm a noob at this & that helped but didn't help

likefunny

NVM, I FINALLY GOT IT TO WORK. I really am a noon 🥲 thank you to everyone who gave suggestions. I've literally been trying to figure this out since last week. ChatGPT didn't even recommend this 🤣

Post Photo
likeuplifting

Glad you figured it out. The other way you could do this is with formulas, specifically substitute function. For example, =substitute(a1,”-“,””) will replace dashes with nothing, thus giving you back the phone number without dashes.

like

ChatGPT can help you

like

you’re VERY good with AI and not with excel? interesting haha

This thread sounds like a troll. But if not, and a find replace all function is over your head, you really need to do some basic excel training.

like

Highlight and replace blanks and parentheses with blank no spaces if that makes sense lol

like
Post Photo
like

Not an excel guru, I would try this: =substitute(cell to change,"(","")

like

Download vsc code and have chatgpt write you a python script to do it. It will be done in ~10 seconds lul.

I kid you not

Post Photo

Text to columns on ()- and the recombine using concatenate formula

Have you tried ctrl+E

=SUBSTITUTE (SUBSTITUTE (A1," (“,”“),”)“,”")

Or

=TEXTJOIN (“”,TRUE,IFERROR (MID (A1,ROW (INDIRECT (“1:”&LEN (A1))),1)*1,“”))

Related Posts

Hinge advice - I (30F) match with descent number of ppl but then most guys don’t initiate the convo. Is this a SF thing or am I doing something wrong? I used to just initiate convo myself, but then in those cases usually don’t get replies. Any tips?

likefunny

Hi Thoughtworkers! Hope all well.
In Thoughtworks offer letter there is no mention of Stocks and joining Bonus. Can someone give insights about it including figure?

like

I have never been able to find good wings in Manhattan or BK - please someone give me the details

like

Currently a L7 at AWS, going for L8. Been offered a VP position at Capgemini. Ignoring the financials, what reasons / factors would you consider important when making a decision. FYI, I am in a Consumer Goods specialist space and think that in the domain I work in, AWS is definitely a tech leader.

like

Hi,

Any idea about the C&SP department in comcast? working culture, WLB and all..

I was selected in Comcast for a developer role.. Will Comcast provide work from home?

so if anyone knows about that please share your thoughts.

like

Did LEK not offer referrals before? That’s 7 straight LEK posts lol

like

Really over this job, but company just started my GC application. I hear it takes about 2 years. Is the GC worth the wait? Does the time go by fast? Lol I feel stuck here & just tired. Send help

like

How the heck can I teach a kindergartener over the computer? It's all hands on in my class for the first couple of months. I have made and bought so many manipulatives but I can't send twenty home with the kiddos. When we do go back to in person learning they want to separate my kiddoes into two groups and I have to move between the two classes to teach. I'll be hauling my stuff on a cart to each class! I'm overwhelmed at the thought of all this.

like

Did any one of you receive any onsite opportunity while working in IBM GBS and after how many years from your joining.

like

Does Accenture give any extra days off as a result of COVID-19?

like

Just got a verbal offer for experienced analyst position at a middle market investment bank in Chicago. What should I expect/negotiate for in terms of salary and bonus?

like
like

How do you apply design factors to IT Audits. Just overheard someone explain 'level of aggregation' for IT Security policies by describing how many people have access to it. Why is this a thing???

like

When is busy season over for the SOX side of stuff? Please tell it doesn’t go past 12/31.

funny

Date ideas in Charlotte?

likehelpful

Has anyone rescinded an offer?

like

With the audit/tax busy season almost wrapping, DM me if you want to make the move into any PwC advisory (highest pay among B4). Happy to chat/refer!

likefunny

Anyone have good fall foliage GA airports in New Hampshire, MA, or Southern Maine?

like

Hello Fishes,
I got an opportunity from NTT Data BPO.
I am currently being hired for nightshift. Are there any chances for getting day shift or mid shift?
Or are there any chances to get a change in shift after getting on boarded?
Will it be okay to discuss this with HR?
Because during interview I said yes to Night Shifts.

like

Additional Posts in Excel Genius

Most used or favorite Shortcut? Ctrl + Z doesn’t count

like

Can dates be grouped just by Month-Year in pivot tables on a Mac without having the year as its own dimension?

Post Photo
like

What’s the keyboard shortcut to get into the “insert options” dialogue when you’ve inserted a new row with Ctrl+Shift+Plus?

like

For a linear regression analysis… the equation of a line is Y=MX+B, where M is the slope. So if I’m measuring a line across 365 days and M=.2, can I say that I have increased production by 20% over a year. Or is production increasing 20% each day?

like

Hi! I’m trying to create a macro that pulls a copy of a sheet from a file with a certain date in the file name into a new workbook. Is there a way to do this?

Anyone have a good source to learn sql

like

Will need to migrate to office 365 soon. What’s the difference for excel from the 2013 version?

likehelpful

Is there a way for me to automate creating columns to do this so I can drag across and create my columns? For example--
1Q2021 2Q2021 3Q2021 4Q2021 2021 1Q2022?

likesmart

Hey everyone! I am working on a report with the intention of updating the products that a specific customer uses. Currently, the data is arranged with the unique ID in Column A, and associated products in Column B. However, each product has its own row so company 1234 might have 5 rows and company 5678 might have 20. What I need to do is collect the product data for each unique company and put it into a single row, separated by a comma(s). I am at a crossroads, any suggestions?

like

I have some data extracted from SAP and though the size is not relatively high lets say about 45000 line items. My excel keeps crashing anything i do with it 🙄! Any clue why it is happening ?

like

Everyone: Excel is a smart app
Excel:

Post Photo
likefunny

New project, new massive excel file that I keep making mistakes on. Tips on error validation and performing QA?

Would anyone be willing to teach me vlookup?

like

Can you share the most useful formulae and built in automation features you have used with the latest version of excel?

like

Can anybody recommend any excel courses to take for financial analysis? Modeling, valuations, etc. Any direction would be helpful.@

likehelpful

Has anybody used the COM library and python to build reports? I'm looking for somebody to chat with about my project.

like

Each country has multiple txns. Some complete, some not. What formula should I use to say if Indonesia has ANY “not complete” then show me the term “not done”. Tried IF(Index Match) but only (cont.)

Post Photo
like

Any suggestions for a formula to calculate a running count of the number of incidences in a list? For example, in a list with A, B, C, B; I want to label A = 1, then the first B =1, and first C = 1, and then the second B = 2, etc. The list is in a column and there are thousands of names. TIA for your help!

Help unhiding columns A-D in excel. I've tried putting A1 in the select cell selecting unhide columns and also youtube to no luck. Please help.

like

Hi all! Is there a way to easily copy over all the rows from multiple sheets into one consolidating sheet? We have 32 teams filling out transactions in their own tabs and need to consolidate all tabs transactions into one long list of transactions. Thank you!

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Scan your QR code to download
Fishbowl app on your mobile

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal