How can I cut the last two words from a column and move them to the next column on the right? I would like to know if there is any order to be able to apply it to the whole file. More precisely, I have to move the postal code from the addresses to another column separately. I am attaching a picture below.
Screenshot 2024-03-15 193309.png
Try
with address in A1
Formula:
Please Login or Register to view this content.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
John's formula will copy the Post Code to the next column. If you want to "move" it, you'd need VBA.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
As TMS noted (and I "missed") you will need VBA if you need to delete the post code from the "FullAddress" entry. Please post a sample file: instructions on "how to" are in the yellow banner at top of the page.
Last edited by JohnTopley; 03-16-2024 at 04:05 AM.
Could be done by Power Query too.
Nothing can be 'moved' with PowerQuery, either. You would end up with a new version of the table with the original still intact.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Well, just press Refresh.
Formula:Please Login or Register to view this content.
You can only get rid of the "second" table if you create a query table (copy of base table). Then delete the base table and refer to the query table.
That is what DJunqueira did here
Last edited by JEC.; 03-16-2024 at 12:49 PM.
Here a variation
PHP Code:let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
pCode = Table.AddColumn(Source, "Postcode_", each Text.AfterDelimiter(Text.Trim([Fulladdress])," ",{1, RelativePosition.FromEnd})),
replace = Table.ReplaceValue(pCode,each [Fulladdress],each Text.Replace([Fulladdress],[Postcode_],"") ,Replacer.ReplaceValue,{"Fulladdress"}),
result = Table.RemoveColumns(replace,{"Postcode"})
in
result
And if you want VBA..
Please Login or Register to view this content.
Last edited by JEC.; 03-16-2024 at 01:18 PM.
JEC you are right.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks