Dear,
could someone help me please, I have a series of excel having sequence of numbers separated by letters like that:
Colu1
1 d 23 h 45 m 2 s
3 h 4 m 55 s
3 d 34 m
22 s
.
.
.
and I would like to separate like that:
Colu(d) Colu(h) Colu(m) Colu(s)
1 23 45 2
0 3 4 55
3 0 34 0
.
.
.
thank you in advance for your help.
Regards,
Which version of Excel are you using?
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.
Dear,
I have 2010 is my version.
Regards,
Then please update your forum profile. Thanks.
In B2 copied across and down:
=IFERROR(--TRIM(MID($A2,IF(FIND(B$1,$A2)-3=0,FIND("d",$A2)-2,FIND(B$1,$A2)-3),2)),"")
Excel 2016 (Windows) 32 bit
d h m s 1 d 23 h 45 m 2 s 1 23 45 2 3 h 4 m 55 s 4 55 3 d 34 m 3 34 22 s 22
Sheet: Sheet1
Last edited by AliGW; 07-02-2020 at 06:21 AM.
Just to clarify - is each line an entry in a single column cell??
Dear AliGW,
thank you sooo much for your help.
Regards,
Updated:
=IFERROR(--TRIM(MID($A2,IF(FIND(B$1,$A2)-3=0,FIND("d",$A2)-2,FIND(B$1,$A2)-3),2)),"")
This will return clean number values.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
AND please update your forum profile!!!
@ Ali, is second row correct??
Dear, regarding the update is it working with version 2010! Regards,
Update (corrected):
=IFERROR(--TRIM(MID($A2,IF(FIND(B$1,$A2)-3=0,FIND(B$1,$A2)-2,FIND(B$1,$A2)-3),2)),"")
Please use this version.
EDIT: Nige - just spotted that myself, but thanks!
But your forum profile says 1989 - please change this to 2010. Thanks.Originally Posted by dell001
You must use the formula in post #7!
Dear, I will and it work perfect it like a magic waoooo thank you I was doing it manual and it take me a lot times.
you save my day thank you again. Regards
PLEASE UPDATE YOUR FORUM PROFILE!!!
The version of Excel you have is incorrect - change it from 1989 to 2010, please.
Then mark the thread as SOLVED.
Dear AliiGW, how to make the thread as Solved ?
Dear, it done I found it thank you so much again have nice day
Fab - thanks!
Am I missing something...
and I would like to separate like that:
Colu(d) Colu(h) Colu(m) Colu(s)
1 23 45 2
0 3 4 55
3 0 34 0
does the d, h, m s not refer column letters...
meaning the values transposed to those columns...
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
Yes, you are missing the fact that the OP wanted columns with those labels in row 1. The solution I offered does what (s)he wants - see post #4 for the layout.
No worries...Was just inquiring...
As this below to me means...Column D, Column H, Column M, Column S
Colu(d) Colu(h) Colu(m) Colu(s)
That's what I wondered to start with, but realised that writing the formula would be easier with the headers on row 1, so had my fingers crossed!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks