Home

Change B3:78/3 to B3[78].3 using find and replace or similar command

%3CLINGO-SUB%20id%3D%22lingo-sub-672288%22%20slang%3D%22en-US%22%3EChange%20B3%3A78%2F3%20to%20B3%5B78%5D.3%20using%20find%20and%20replace%20or%20similar%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-672288%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20looking%20have%20a%20large%20file%20with%20data%20that%20looks%20like%20such..%3C%2FP%3E%3CP%3EB3%3A78%2F2%3C%2FP%3E%3CP%3EB3%3A78%2F3%3C%2FP%3E%3CP%3Eand%20so%20on...%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20270px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117280i646C7BB170F1C165%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22email%20pic%20delete%20me%201.PNG%22%20title%3D%22email%20pic%20delete%20me%201.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20want%20to%20change%20them%20as%20quickly%20as%20possible%20to%20look%20like%3C%2FP%3E%3CP%3EB3%5B78%5D.2%3C%2FP%3E%3CP%3EB3%5B78%5D.3%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20275px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117278i179662EF7D1127B2%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22email%20pic%20delete%20me%202.PNG%22%20title%3D%22email%20pic%20delete%20me%202.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ECan%20I%20change%20all%20at%20once%2C%20certain%20aspects%20of%20data%20in%20cells%20rather%20than%20have%20to%20do%20them%20one%20at%20a%20time%3F%3C%2FP%3E%3CP%3ESpecifically%2C%20changint%20the%20%22%3A%22%20to%20%22%5B%20%5D%22%20and%20the%20%22%2F%22%20to%20%22.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-672288%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-672359%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20B3%3A78%2F3%20to%20B3%5B78%5D.3%20using%20find%20and%20replace%20or%20similar%20command%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-672359%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F179391%22%20target%3D%22_blank%22%3E%40Deen%20Brown%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Deen%2C%3C%2FP%3E%3CP%3Eyou%20can%20do%20it%20with%20the%20following%20macro%20-%20look%20at%20the%20attached%20file%2C%20too.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20ReplacePart()%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20Sheet1%3CBR%20%2F%3E%3CBR%20%2F%3E.Range(%22A%3AA%22).Replace%20What%3A%3D%22%3A%22%2C%20Replacement%3A%3D%22%5B%22%2C%20_%3CBR%20%2F%3ELookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20MatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse%2C%20ReplaceFormat%3A%3DTrue%3CBR%20%2F%3E%3CBR%20%2F%3E.Range(%22A%3AA%22).Replace%20What%3A%3D%22%2F%22%2C%20Replacement%3A%3D%22%5D.%22%2C%20_%3CBR%20%2F%3ELookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20MatchCase%3A%3DFalse%2C%20SearchFormat%3A%3DFalse%2C%20ReplaceFormat%3A%3DTrue%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%20from%20germany%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20title%3D%22VBA-Tanker%22%20href%3D%22http%3A%2F%2Fwww.vba-tanker.com%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-Tanker.com%20-%20a%20database%20full%20of%20macros%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Deen Brown
New Contributor

Hi, looking have a large file with data that looks like such..

B3:78/2

B3:78/3

and so on...

email pic delete me 1.PNG

I want to change them as quickly as possible to look like

B3[78].2

B3[78].3

email pic delete me 2.PNG

Can I change all at once, certain aspects of data in cells rather than have to do them one at a time?

Specifically, changint the ":" to "[ ]" and the "/" to "."

 

1 Reply

@Deen Brown 

Hi Deen,

you can do it with the following macro - look at the attached file, too.

 

Sub ReplacePart()

With Sheet1

.Range("A:A").Replace What:=":", Replacement:="[", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True

.Range("A:A").Replace What:="/", Replacement:="].", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True

End With

End Sub

 

Best regards from germany

Bernd

www.vba-Tanker.com - a database full of macros

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies