Home

Trying to create a simple macro in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-809576%22%20slang%3D%22en-US%22%3ETrying%20to%20create%20a%20simple%20macro%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809576%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20update%20a%20spreadsheet%20that%20has%20copied%20values%20from%20Bank%20statements.%20The%20bank%20statements%20always%20include%20text%20after%20the%20amount%2C%20such%20as%20%24212.89%20USD%20or%20%24212.89%20CAD.%26nbsp%3B%20I%20can%20easily%20sort%20the%20values%20by%20currency%20type%2C%20but%20i%20can't%20seem%20to%20get%20a%20macro%20to%20work%20that%20will%20simply%20erase%20the%20spaceUSD%20or%20space%20CAD%20out%20of%20the%20cell%20and%20leave%20the%20%24212.89.%26nbsp%3B%20It%20has%20got%20to%20be%20simple%26nbsp%3B%20but%20I%20can't%20figure%20it%20out.%20Ideally%20the%20macro%20would%20clean%204%20to%20six%20rows%20at%20a%20time%2C%20for%20example...%3C%2FP%3E%3CP%3EFrom%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20To%3C%2FP%3E%3CP%3E%24212.89%20UDS%26nbsp%3B%20%24212.89%3C%2FP%3E%3CP%3E%24143.24%20USD%26nbsp%3B%20%24143.25%3C%2FP%3E%3CP%3E%2489.00%20UDS%20%26nbsp%3B%26nbsp%3B%20%2489.00%3C%2FP%3E%3CP%3E%24961.23%20USD%26nbsp%3B%20%24961.23%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-809576%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-809625%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20create%20a%20simple%20macro%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809625%22%20slang%3D%22en-US%22%3E%3CP%3EHI%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393991%22%20target%3D%22_blank%22%3E%40K_DeMaere%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20case%20bank%20uses%20the%20same%20format%20then%20your%20requirement%20can%20simply%20be%20achieved%20using%20below%20formula%20(no%20need%20for%20macro)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20color%3D%22%230000FF%22%3E%3DLEFT(A1%2C(LEN(A1)-4))%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20sample%20file%20is%20also%20attached%20for%20your%20reference.%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20it%20works%20for%20you.%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-809857%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20create%20a%20simple%20macro%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809857%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393991%22%20target%3D%22_blank%22%3E%40K_DeMaere%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20Ctrl%2BH%20and%20Replace%20All%20%22%20USD%22%20(type%20without%20apostrophes)%20on%20nothing%20-%20for%20entire%20worksheet%20or%20selected%20range%3C%2FP%3E%3C%2FLINGO-BODY%3E
K_DeMaere
Occasional Visitor

I need to update a spreadsheet that has copied values from Bank statements. The bank statements always include text after the amount, such as $212.89 USD or $212.89 CAD.  I can easily sort the values by currency type, but i can't seem to get a macro to work that will simply erase the spaceUSD or space CAD out of the cell and leave the $212.89.  It has got to be simple  but I can't figure it out. Ideally the macro would clean 4 to six rows at a time, for example...

From              To

$212.89 UDS  $212.89

$143.24 USD  $143.25

$89.00 UDS    $89.00

$961.23 USD  $961.23

 

Can someone help?

2 Replies

HI @K_DeMaere 

 

In case bank uses the same format then your requirement can simply be achieved using below formula (no need for macro):

 

=LEFT(A1,(LEN(A1)-4))

 

A sample file is also attached for your reference.

Please let me know if it works for you.

Tauqeer

@K_DeMaere 

You may Ctrl+H and Replace All " USD" (type without apostrophes) on nothing - for entire worksheet or selected range

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