Custom format
There is one problem that needs help from experts.
I got data:
Cell A1: AA-BB-999-xxxxx-zz-Abcdefghij Klmno 9, PRSTUVW
Cell A2: AA-BB-888-zzzzz-zz-Abcdef Ghijk 8, LMNOPRSTUV
...
...
...
In B1 and C1, B2 and C2 i need this:
B1: Abcdefghij Klmno 9
C2: PRSTUVW
B2: Abcdef Ghijk 8
C2: LMNOPRSTUV
They are not same length.
Hi,
Using your supplied data,
B1=MID(A1,20,FIND(",",A1)-20)
C1=RIGHT(A1,LEN(A1)-FIND(",",A1))
Please disregard anything in the above post. It may well have been edited without my consent, as has been the case with several posts and threads recently.
Originally Posted by sweep
Second one works, while 1st only for 1 case. I need it for all cases in different number of characters.
How about an example of the data that shows where the lengths and formats change?
Please disregard anything in the above post. It may well have been edited without my consent, as has been the case with several posts and threads recently.
Re: Excel, custom format
I copied the formula again and now it works, strange
Both work, thanks!
Problem arised again, and i need help again
Data is mostly the same, i got one more "," and now formula doesnt work
Cell A1: AA-BB-999-xxxxx-zz, Abcdefghij Klmno 9, PRSTUVW
Cell A2: AA-BB-888-zzzzz-zz, Abcdef Ghijk 8, LMNOPRSTUV
Whats the correct formula for this (same request as before)?
Based on your latest examples perhaps you could use:
B1:
=TRIM(REPLACE(LEFT(A1;LOOKUP(9,99E+307;FIND({1\2\3\4\5\6\7\8\9\0}&",";A1)));1;19;""))
(above assumes the address always ends with a number and is followed by a comma)
C1:
=TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";LEN(A1)));LEN(A1)))
(above assumes the last section never contains spaces)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
It's not an address, and it doesnt always end with number, sry that i didn't specify that.
Last section can contain spaces
Post a small sample file that reflects the variety of strings you have to deal with - and also outline via adjacent cells the desired output(s).
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Here:
Btw. The point is i need these things surrounded by "," extracted.
Yet your earlier strings did not follow this pattern
Are you now saying your initial examples (post #1) were not indicative of your real data ? ie you only have comma based strings ?
If not, the point I was trying to make was, provide an example which illustrates all variations you are trying to deal with (eg hyphen, comma etc...)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Yep, first one was what i needed before, now it changed "-" to ","
Well OK I confess I'm confused - I had presumed you wanted one formula to account for both constructs.
B1: =TRIM(MID(SUBSTITUTE($A1;",";REPT(" ";LEN($A1)));LEN($A1)*COLUMNS($B1:B1);LEN($A1)))
copied across matrix B1:C5
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks