Extract substring

K

kd12345

New Member
Joined
Oct 16, 2014
Messages
6
How do I extract a substring in the following example?:


Cell A1 = WATER IBC - P275G-1
Result needed = 275
Text and number of characters in each cell will vary, so I need to find the position "- P" for each cell then return the 3 numbers after P.
I
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
is there a way to slip "OR" into the formula? For instance:


Cell A1 = Water IBC - P275g-1
Cell A2 = Water IBC - SS330G-1

Is it possible to search for " - P" or " - SS" but still return only the 3 characters after "P" or "SS"?
 
Upvote 0
Try

=IF(ISNUMBER(FIND("- P",A1)),MID(A1,FIND("- P",A1)+3,3),IF(ISNUMBER(FIND("- SS",A1)),MID(A1,FIND("- SS",A1)+4,3),""))
 
Upvote 0
kd12345 said:
is there a way to slip "OR" into the formula? For instance:


Cell A1 = Water IBC - P275g-1
Cell A2 = Water IBC - SS330G-1

Is it possible to search for " - P" or " - SS" but still return only the 3 characters after "P" or "SS"?
Click to expand...
Is the number always followed by three characters (letter/dash/digit)? If so...

=MID(A1,LEN(A1)-5,3)
 
Upvote 0
kd12345 said:
No, the number of characters that follow varies.
Click to expand...
You should consider showing such variability in the examples you include in any future posts as it makes things easier for us. Another question then... will there ever be any numbers in the text before the number you want to extract? If not...

=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),3)
 
Upvote 0
Sorry, I will try to keep that in mind. Number of characters before and after " - P" & " - SS" strings both vary. I changed the "find" to "search" in the following function and it worked.
Thank all for your help!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Return pattern of text
Replies
2
Views
80
mib1019
M
W
  • Solved
Extract the characters between 2 spaces in a string
Replies
5
Views
143
Fluff
Fluff
K
  • Question
Extraction of substring
Replies
5
Views
452
mohadin
M
C
  • Solved
Extra text after specific characters
Replies
4
Views
71
Fluff
Fluff
C
  • Question
Extracting Specific Fields from multiple PDFs with VBA (Excel 2019)
Replies
1
Views
179
chickyguy
C

Forum statistics

Threads
1,214,522
Messages
6,120,022
Members
448,939
Latest member
Leon Leenders

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top