Excel Forumlae Question - any excel wizz here?

p1tse

p1tse

Suspended / Banned
Messages
2,391
Edit My Images
No
Basically say my data looks like this in each cell

john e-12345
smith e-123
paul e-4567
joe e-98
blogg e-233234

how can i split out just the numbers on the end?

can't do a right(text,number)

only way at the moment is to find and replace e- with something like @ and then delimit with @

but is there a forumulae?
 
What format is the source data in? Text file, CSV or an actual Excel spreadsheet?
 
actual excel spreadsheet as General
 
Right, assuming the intial text is always "e-" and the cell with the data in is "G4" then:

=RIGHT(G4,LEN(G4)-2)

Should work.

Paul
 
or you can use the text to column function (found under the data menu) using the hyphen as the delimeter
 
grumpybadger said:
Right, assuming the intial text is always "e-" and the cell with the data in is "G4" then:

=RIGHT(G4,LEN(G4)-2)

Should work.

Paul
Click to expand...

no luck
that forumale doesn't have e- in it to pick up?
 
Hippo_MC said:
or you can use the text to column function (found under the data menu) using the hyphen as the delimeter
Click to expand...

i want to use a forumale as have a big spreashssheet with similar scenarios.
delimit adds columns etc.
 
This should do it.

=MID(A2,SEARCH("e-",A2,1)+2,(LEN(A2)))

in this example I've used Cell A2
 
Hippo_MC said:
or you can use the text to column function (found under the data menu) using the hyphen as the delimeter
Click to expand...

can this be done in a forumale, as i don't want to add more columns on delimit
 
AndrewA123 said:
This should do it.

=MID(A2,SEARCH("e-",A2,1)+2,(LEN(A2)))

in this example I've used Cell A2
Click to expand...

sweet

after having a play to get it right, this works i think

thanks all
 
great news :thumbs:
 
p1tse said:
no luck
that forumale doesn't have e- in it to pick up?
Click to expand...

Have you tried it? Works on the data you presented in the problem statement. Why doesn't it work? Is the left data no always "e-"?

If you just need to strip off the left 2 characters (as your example suggests) then it works just fine on my PC - if not, can you please clarify the problem statement?
 
ah, thanks again. sorry it wasn't clear. it was in one cell. but grumpybadger, i like yours too if it was sepeate, which i have noted for future reference
 
i'm sure i'll have more to come lol

great having excel wizz's here ;-)
 
update question.

what forumale to use if i just want the names with the above examples?

i was thinking of something like = search(" ",a1,1) but not sure if right or left statement etc. as mid takes things to the right of the search
 
Assuming one name only in each row (ie: Jim not Jim Smith) then try this:

=LEFT(A2,(SEARCH(" ",A2,1)))

Alternatively you could use this, which again uses the e- as a separator:

=LEFT(A2,(SEARCH("e-",A2)-2))
 
Last edited:
AndrewA123 said:
Assuming one name only in each row (ie: Jim not Jim Smith) then try this:

=LEFT(A2,(SEARCH(" ",A2,1)))

Alternatively you could use this, which again uses the e- as a separator:

=LEFT(A2,(SEARCH("e-",A2)-2))
Click to expand...
thanks i'll give it ago tomorrow.

tried something similar, but it just came up with a number count i think
 
AndrewA123 said:
Assuming one name only in each row (ie: Jim not Jim Smith) then try this:

=LEFT(A2,(SEARCH(" ",A2,1)))

Alternatively you could use this, which again uses the e- as a separator:

=LEFT(A2,(SEARCH("e-",A2)-2))
Click to expand...

perfect thanks
 
No problem mate :thumbs:
 
You must log in or register to reply here.
Back
Top