Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

FIND - Extract Text in a Specific Order

0
310 Wattle StUltimo, 2007, NSW

How the way i get   NSW2007   from this text?

Post Edited
Title: Title was not descriptive.
Tags: Tags were updated to reflect the topic of the question.
Answer
Discuss

Answers

1

Hello John123,

If texts 2007 and NSW always have same length, following formula can work:

 =RIGHT(RIGHT(SUBSTITUTE(B3,",",""),8),3)&LEFT(RIGHT(SUBSTITUTE(B3,",",""),8),4)

Please try and let me know.

Regards
Basilio

Discuss

Discussion

Hi Basilio, I think you meant for the first REPLACE() function to be a SUBSTITUTE() function like in the second part of the formula.
=RIGHT(RIGHT(SUBSTITUTE(B3,",",""),8),3) & LEFT(RIGHT(SUBSTITUTE(B3,",",""),8),4)
don (rep: 1989) May 20, '20 at 4:04 pm
Yes, you are right

Thanks.
Basilio (rep: 105) May 20, '20 at 5:24 pm
You're welcome)
don (rep: 1989) May 21, '20 at 3:30 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login